A
alexis suzat via OfficeKB.com
Hello,
The code and page references that I give here are from "Excel 2003 VBA,
Wrox" - very useful book for the non-professional programmer that I am.
My question is the following :
- I use VBA in Excel, and would like to use Excel itself as a data source
("Using ADO with Non-Standard Data Sources, Querying Microsoft Excel
Workbooks", p239). 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. I need to keep my
Excel session since I get real-time feeds, make calulations, and they
manipulate the calculated data with SQL.
- The table to query is located in the active workbook, as opposed to being
saved in another, inactive, file. I use a range name "TESTRNG" to define
this table in the Sheet1.
- I then dump the SQL result in a range located in the Sheet1, cell C10,
with the CopyFromRecordset statement.
- 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
Alex.
Using a very slightly modified version of the code provided p241 :
==========================================================
Sub QueryWorkSheet()
Dim Recordset As ADODB.Recordset
Dim ConnectionString As String
ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=Excel 8.0;"
Dim SQL As String
'TESTRNG is an excel range name, which defines the table to query, with
field names in the first, header row, and records in other rows.
SQL = "SELECT * FROM TESTRNG;"
Set Recordset = New ADODB.Recordset
On Error GoTo Cleanup
Call Recordset.Open(SQL, ConnectionString,
CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly,
CommandTypeEnum.adCmdText)
Call Range("C10").CopyFromRecordset(Recordset)
Cleanup:
Debug.Print Err.Description
If (Recordset.State = ObjectStateEnum.adStateOpen) Then
Recordset.Close
End If
Set Recordset = Nothing
End Sub
==========================================================
The code and page references that I give here are from "Excel 2003 VBA,
Wrox" - very useful book for the non-professional programmer that I am.
My question is the following :
- I use VBA in Excel, and would like to use Excel itself as a data source
("Using ADO with Non-Standard Data Sources, Querying Microsoft Excel
Workbooks", p239). 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. I need to keep my
Excel session since I get real-time feeds, make calulations, and they
manipulate the calculated data with SQL.
- The table to query is located in the active workbook, as opposed to being
saved in another, inactive, file. I use a range name "TESTRNG" to define
this table in the Sheet1.
- I then dump the SQL result in a range located in the Sheet1, cell C10,
with the CopyFromRecordset statement.
- 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
Alex.
Using a very slightly modified version of the code provided p241 :
==========================================================
Sub QueryWorkSheet()
Dim Recordset As ADODB.Recordset
Dim ConnectionString As String
ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=Excel 8.0;"
Dim SQL As String
'TESTRNG is an excel range name, which defines the table to query, with
field names in the first, header row, and records in other rows.
SQL = "SELECT * FROM TESTRNG;"
Set Recordset = New ADODB.Recordset
On Error GoTo Cleanup
Call Recordset.Open(SQL, ConnectionString,
CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly,
CommandTypeEnum.adCmdText)
Call Range("C10").CopyFromRecordset(Recordset)
Cleanup:
Debug.Print Err.Description
If (Recordset.State = ObjectStateEnum.adStateOpen) Then
Recordset.Close
End If
Set Recordset = Nothing
End Sub
==========================================================