PDA

View Full Version : Excel Sheets Opens Itself Again After ADO Query !



redan
08-28-2007, 03:16 AM
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
08-28-2007, 04:26 AM
Where/how is the extra session created?

redan
08-28-2007, 04:55 AM
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
08-28-2007, 04:55 AM
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
08-28-2007, 05:29 AM
Not here it doesn't.

Some setting that yu have?

redan
08-28-2007, 05:53 AM
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
08-28-2007, 09:31 AM
What version of Excel is it run under?

redan
08-28-2007, 09:40 AM
Hello Malik,

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

Thanks for ur help.

Bob Phillips
08-28-2007, 10:07 AM
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.aspx?scid=kb;en-us;319998&Product=xlw