Excel Sheets Opens Itself Again After ADO Query ! Please Help It's Urgent !

[Nader]

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

 

[Guest]

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]

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

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.

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

 

[Guest]

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.

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

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.

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

 

[Nader]

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

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.

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

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.


:

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

 

[Nader]

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

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

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.

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" <[email protected]> a écrit dans le message de (e-mail address removed)...
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.


:

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

 

[Guest]

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.

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

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

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.




:

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" <[email protected]> a écrit dans le message de (e-mail address removed)...
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.


:

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

 

[Nader]

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

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.

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

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" <[email protected]> a écrit dans le message de
(e-mail address removed)...
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.




:

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" <[email protected]> a écrit dans le message de
(e-mail address removed)...
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.


:

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

 

[Nader]

Hello Jack,

Do you know if it's possible to get session id of excel so I would ban the
user from running it the second session.

Thanks in advance.

Cheers,

Nader

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

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.

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" <[email protected]> a écrit dans le message de (e-mail address removed)...
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" <[email protected]> a écrit dans le message de
(e-mail address removed)...
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.




:

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" <[email protected]> a écrit dans le message de
(e-mail address removed)...
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.


:

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