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

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

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" a écrit dans le message de news:
...
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