Using ADO to Query Excel

[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 Function Simulation1() As Variant

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 Function

 

[Guest]

Hi,

My guess is you have an error occure but handled with
Sub blabla()
On Error Resume Next
Simulation1
End Sub

Maybe error while closing connection or setting object with nothing

 

[Nader]

Hello Halim,

Thanks for the reply. I don't have any "On Error Resume Next" in my code.
But, I also tried the code without function and I still have the same
problem.


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

Hi,

My guess is you have an error occure but handled with
Sub blabla()
On Error Resume Next
Simulation1
End Sub

Maybe error while closing connection or setting object with nothing
--
Regards,

Halim

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 Function Simulation1() As Variant

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 Function