Re: Excel Sheets Opens Itself Again After ADO Query ! Please Help

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hello Jack,

Excellent, I just tried your way (which works great) and I'm now looking
into getting the vba code so I could do all the steps automatically.

Thanks a lot for all the help and the informations.

Cheers,

Nader

"jack" <jack@xxxxxxxxxxxxxxxxxxxxxxxxx> a écrit dans le message de news:
28E400DE-E4BA-43A4-AA9E-1E8BB8541510@xxxxxxxxxxxxxxxx
Ok, this DAO/ADO method doesn't seem stable enough in your circumstance.

An easy way to explore the ODBC method is to use the built in
functionality
in Excel. I'm using Excel 2003 so hopefully its similar to your setup.
Through the menus "Data/Import External Data/New Database Query" go to the
database tab and select "Excel Files" (if this is not there use "New Data
Source" to create one). You should then be asked for the workbook to query
against. Select your file - presumably the file your in already. You
should
then be shown the query wizard. If you don't get the query wizard and get
error message that no visible tables can be found then cancel completely
out
of the wizard and then create a named range of your table in the sheet you
want to query against - sorry I should have mentioned this first (I will
assume you know how to create a named range here). Anyway back to the
query
wizard. If you now select the whole table or specific columns (fields) and
send them over to "Columns in your query" using the right chevron. Click
"Next" three times and select "View data or edit query in Microsoft Query"
then click "Finish". You should now be in Microsoft Query - looks similar
to
MS Access queries. You should see a SQL button now where you can edit the
SQL
direct. Change the SQL so you get the desired results - the SQL should be
similar to what you were using before. When your finished then go to
"File/Return Data To Microsoft Office Excel". Now you should be asked
where
you want the results to go in your workbook, choose a suitable location
that
is safe. Bingo, your done. You have now connected to your file using a
form
of ODBC (its all very similar to the connection layer that DAO/ADO uses).
When you want your data to update all you do is right click your query
data
and choose "Refresh Data" and you get an on-line update.

I hope I wasn't showing you "how to suck eggs" and you found this useful.
Without knowing exactly what your trying to achieve, sometimes there is
easier ways to get the same results - the problem is, that there is so
many
ways to do the same thing and it all can get confusing and you lose sight
of
what your doing.

Enjoy - let me know how you get on.

BTW I think you would be lucky to get a response from Microsoft unless
money
is involved.

Cheers,
Jack.



"Nader" wrote:

Hello Jack,

I tried the code once again with DAO and unfortunately I have the same
issue
as before.

That's what I did excatly :

1) I have an excel session already open (Session 1) on my
Windows desktop.

2) I open a new, fresh, Excel session (Session2) and open my file, with
the
table, and the VBA code.

3) I run the code in my file, Session2: it does work, but the unwanted
effect is that my file opens itself again, as Read-Only, in the other
Session1!

Could you please check again.

Thanks in advance.

Cheers


"Nader" <redan@xxxxxxxxx> a écrit dans le message de news:
OqSmOQv6HHA.5984@xxxxxxxxxxxxxxxxxxxxxxx
Hello Jack,

Thanks for code.

I've tried your code and it's working perfectly. The reason why I don't
want the close the read-only file after the query has been done is
simple.
The excel I'm working on is pretty big (more 40mb), so when it open it
takes a bit of time and on top of that it contains data which are
retrieve
from Bloomberg thru functions.
So, if everytime I execute my query I have to wait til the file is open
I'd rather not use SQL queries cuz reason why choose SQL was because it
was a lot more faster than other alternative.

As for "alternative 2", don't worry I'm using SQL for something more
complex than justing counting records.

Finally "alternative 3", I had no idea that I could use another
provider
than the usual "Provider=Microsoft.Jet.OLEDB.4.0;". I will try to find
other provider cuz DAO is only a temporary solutions (I read on
microsoft
website that ADO has replaced DAO).

Do you think, I could contact Microsoft hoping than can resolve this
issue
?

Thanks again for ur help!

Cheers,

Nader


"jack" <jack@xxxxxxxxxxxxxxxxxxxxxxxxx> a écrit dans le message de
news:
8AF9F644-A26A-4AB9-9579-8CE37380EC7E@xxxxxxxxxxxxxxxx
Hi Nader,

I found my code that utilises the DAO technique. I changed it to be
more
fitting to your requirement and also did a slight variation on your
SQL
to
show a different way to count the number of records (you could use the
exact
same SQL you used in the ADO if you want). It appears to get round the
issue
of re-opening the same file again. I ran it many times in slightly
different
scenarios and I couldn't get it to open the file again like in the ADO
way.
I'm using Excel 2003, I hope you get the same results.

Thinking aloud before you look at the code :-

Alternative one: allow the ADO to open the file and within your code
locate
the read only version and close it. After you have done your
calculations
of
course.

Alternative two: there is far easier ways to count records in an open
file
that doesn't require SQL. I assume you are using the SQL for something
more
complex than counting records.

Alternative three: I did read somewhere on the net that it might be
Microsoft Jet engine that is causing the problem and maybe if you use
another
ODBC connection you might get better results.

Here goes anyway ...........

***********************************************
Sub test_DAO()
Dim my_file As String
Dim my_db As DAO.Database, my_rs As DAO.Recordset

my_file = "C:\test.xls" 'path and name of file

Set my_db = OpenDatabase(Name:=my_file, Options:=False,
ReadOnly:=True, _
Connect:="Excel 8.0; HDR=Yes")
'declare database with configuration
Set my_rs = my_db.OpenRecordset("select * from [Sheet1$] where
[PX_LAST]
>
20") 'declare recordset and execute SQL statement

my_rs.MoveLast 'ensure recordset is aware of all records

MsgBox "Total number of records retrieved are " & my_rs.RecordCount
'display
record count

'close database and recordset
my_rs.Close
my_db.Close

'disassociate database and recordset
Set my_rs = Nothing
Set my_db = Nothing

End Sub
***************************************************

I don't fully understanding why you get your side effects but maybe
this
post might help.

Cheers,
Jack.




"Nader" wrote:

Hello Jack,

Thanks a lot for your help.

Well, I've read the article regarding ADO issue with memory leaks but
I
have
no idea if it's affecting my macro.
Anyways, I do need to SQL against an open workbook (i'll be running
the
macro from the same file I want the result, so my excel file will
always
be
open)

I'd be glad to have a few lines of code to try it on my worksheet and
hope
that will resolve my problem.

Thanks in advance.

Cheers

Nader

"jack" <jack@xxxxxxxxxxxxxxxxxxxxxxxxx> a écrit dans le message de
news:
4A2868A7-DFBE-4021-B58E-31B2BF38C3A7@xxxxxxxxxxxxxxxx
Not sure if this answers your question but might be helpful.

I tried your code myself and it had the same results and re-opened
the
workbook again in read only mode - how strange.

When you run the code from another workbook and the workbook you
read
is
closed it works fine. Do you need to SQL against an open workbook,
can
it
be
closed ?

I believe there is problems using ADO on an open workbook. You need
to
look
at this notice http://support.microsoft.com/kb/319998 about some
pitfalls.

In the past I have used DAO to read an open workbook and it works
ok.
DAO
will still allow you SQL against the sheets as you want. If you
want
to
persue this angle I could dig out a snippet of my code that might
help -
just
let me know and I will post here.

Hope this opens the discussion more.

Jack.


"Nader" wrote:

My question is the following:

- I use VBA in Excel, and would like to use Excel itself as a data
source
(Using ADO). I'd like to keep a table in excel and use SQL
expression
power to manipulate the data in the table (selection, grouping,
filtering.),
rather than using the usual Excel lookups.

- This does work, but I have an annoying secondary effect with the
following
environment:

1) I have an (unrelated) excel session already open (Session 1) on
my
Windows desktop.

2) I open a new, fresh, Excel session (Session2) and open my file,
with
the
table, and the VBA code.

3) I run the code in my file, Session2: it does work, but the
unwanted
effect is that my file opens itself again, as Read-Only, in the
other
Session1!

I do not know what went wrong. It could be great if you could put
me
on
the
right tracks.

Regards

Public Sub Simulation3()

strPathExcelFile_FILTER = ThisWorkbook.FullName

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathExcelFile_FILTER
&
";" &
_
"Extended Properties=Excel 8.0;"


objRecordSet.Open "SELECT COUNT(*) AS resultat FROM
[SHEET1$A1:IV20]
WHERE [PX_LAST] > 20", objConnection, adOpenForwardOnly,
adLockReadOnly

Simulation.Label2.Caption = objRecordSet.fields("resultat")

objRecordSet.Close
objConnection.Close
Set objConnection = Nothing
Set objRecordSet = Nothing

End Sub













.

Relevant Pages

  • RE: Macro
    ... Then post the query results. ... My data is in an Excel file called ... Microsoft SQL Server 2000, which requires a server, VBA or Microsoft Query ... VBA code in excel suddenly became un-compileable. ...
    (microsoft.public.excel.programming)
  • Re: Excel Sheets Opens Itself Again After ADO Query ! Please Help
    ... I'm using Excel 2003 so hopefully its similar to your setup. ... You should see a SQL button now where you can edit the SQL ... I have an excel session already open on my ... Hope this opens the discussion more. ...
    (microsoft.public.excel.programming)
  • RE: Excel & MS Query caused compile errors
    ... My data is in an Excel file called ... The format of it were distorted after copying and pasting. ... Microsoft SQL Server 2000, which requires a server, VBA or Microsoft Query ...
    (microsoft.public.excel.programming)
  • Re: Excel Sheets Opens Itself Again After ADO Query ! Please Help
    ... Do you know if it's possible to get session id of excel so I would ban the ... You should see a SQL button now where you can edit the ... Hope this opens the discussion more. ...
    (microsoft.public.excel.programming)
  • RE: Problem populating Excel Chart with Access VBA
    ... Another way to approach this is to use Excel's Data Query functionality. ... Select MS Access Database, ... "Return data to Microsoft Office Excel". ... When the form opens it calls the AddChart Subroutine the start of which I've ...
    (microsoft.public.access.formscoding)