Excel Sheets Opens Itself Again After ADO Query !

[redan]

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.

Thanks in advance for your help.

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

[Bob Phillips]

Where/how is the extra session created?

[redan]

Well,

If you put this procedure inside a module and run it a couple of time. It will open the same file in another excel session in read-only mode.

I've attached to this post an example.

Thanks again for ur help.

Cheers

Nader

[redan]

Well,

If you put this procedure inside a module and run it a couple of time. It will open the same file in another excel session in read-only mode.

I've attached to this post an example.

Thanks again for ur help.

Cheers

Nader

[Bob Phillips]

Not here it doesn't.

Some setting that yu have?

[redan]

Well, I've tried on 6 different pc and it always does it.
What I do, is simply, open the file inside the first session of excel and I open another one which is empty. It should after I while open the same file on the second session.
It's really annoying ... I read different post in which people had exactly the same problem as me but they didn't get an answer.
Thanks

[malik641]

What version of Excel is it run under?

[redan]

Hello Malik,

It run under Microsoft Excel 2002 SP3 with XP Pro SP2.

Thanks for ur help.

[Bob Phillips]

So you want to query an open Excel workbook, in fact the same workbook, using ADO? How perverse, to go through all of those layers to come back to where you started from.

Anyway, the answer might lie here http://support.microsoft.com/default...98&Product=xlw