Tuesday, December 04, 2007

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


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

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

If you use the above code for Excel 2010, you need to change the connection string to  the following

ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Om\Documents\SubFile.xlsx;Extended Properties=Excel 12.0;Persist Security Info=False"
Else it will thrown an 3706 Provider cannot be found. It may not be properly installed. error



See also:

Opening Comma Separate File (CSV) through ADO

Using Excel as Database using VBA (Excel ADO)

Create Database with ADO / ADO Create Database

ADO connection string for Excel

9 comments:

  1. Very good, finally a comprehensive tutorial on how to use SQL statements on VBA and excel.

    ReplyDelete
  2. I have Excel 2010 and i'm trying to run you excelente code, but i'm getting run-time error '3706' saing the provider cannot be found.
    Can anyone help me.

    Thanks

    ReplyDelete
    Replies
    1. "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Macro;HDR=Yes"""

      Delete
  3. First of all Thanks for the tutorial.

    But i'm getting run-time error 3706, saying the Provider cannot be found.
    I got Excel 2010 installed.

    Please i need help!

    ReplyDelete
  4. @OCosta : You need to change the ConnectionString Modified the post accordingly

    ReplyDelete
  5. Hi

    I am trying to use this code with a where clause... but it is giving Syntax error

    Please help.

    Thanks

    ReplyDelete
    Replies
    1. did you have the headers on the first row?

      Delete
  6. I know how to use Ms Excel Vlookup formula now i am able to find the numbers from one sheet to another.Excel Training NYC

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group