N
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
- 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