Created
July 8, 2011 23:06
-
-
Save evandrix/1073045 to your computer and use it in GitHub Desktop.
ADO: SQL query in XLS
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Query Table with Excel as Data Source | |
Query tables can be of great help if you need to extract particular data from a data source | |
It represents a worksheet table built from data returned from an external data source, such as an SQL server or a Microsoft Access database. The QueryTable object is a member of the QueryTables collection | |
However, it need to be SQL server or a Microsoft Access database always. You can use CSV file or our fellow Microsoft Excel spreadsheet as a data source for QueryTable | |
Here is one such example, which extracts data from MS Excel sheet | |
Use the Add method to create a new query table and add it to the QueryTables collection. | |
You can loop through the QueryTables collection and Refresh / Delete Query Tables | |
---------------------- | |
Sub Excel_QueryTable() | |
Dim oCn As ADODB.Connection | |
Dim oRS As ADODB.Recordset | |
Dim ConnString As String | |
Dim SQL As String | |
Dim qt As QueryTable | |
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\SubFile.xls;Extended Properties=Excel 8.0;Persist Security Info=False" | |
Set oCn = New ADODB.Connection | |
oCn.ConnectionString = ConnString | |
oCn.Open | |
SQL = "Select * from [Sheet1$]" | |
Set oRS = New ADODB.Recordset | |
oRS.Source = SQL | |
oRS.ActiveConnection = oCn | |
oRS.Open | |
Set qt = Worksheets(1).QueryTables.Add(Connection:=oRS, _ | |
Destination:=Range("B1")) | |
qt.Refresh | |
If oRS.State <> adStateClosed Then | |
oRS.Close | |
End If | |
If Not oRS Is Nothing Then Set oRS = Nothing | |
If Not oCn Is Nothing Then Set oCn = Nothing | |
End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment