Performing SQL queries on an Excel Table within a Workbook with VBA Macro

Question

I am trying to make an excel macro that will give me the following function in Excel    SQL  SELECT heading 1 FROM Table1 WHERE heading 2  foo      Allowing me to search  and maybe even insert  data in my Workbook s Tables using SQL queries   This is what I have done so far   Sub SQL    Dim cn As ADODB Connection Dim rs As ADODB Recordset  strFile   ThisWorkbook FullName strCon    Provider Microsoft ACE OLEDB 12 0 Data Source    amp  strFile    amp    Extended Properties   Excel 12 0 HDR Yes IMEX 1      Set cn   CreateObject  ADODB Connection   Set rs   CreateObject  ADODB Recordset    cn Open strCon  strSQL    SELECT   FROM  Sheet1 A1 G3    rs Open strSQL  cn  Debug Print rs GetString  End Sub   My script works like a charm with hardcoded ranges such as the one in the snippet above  It also works very well with static named ranges   However  it won t work with either dynamic named ranges or TABLE NAMES which is the most important to me   The closest I have found of an answer is this guy suffering from the same affliction  http   www ozgrid com forum showthread php t 72973  Help anyone   Edit  I have cooked this so far  I can then use the resulting name in my SQL queries  The limitation is that I need to know on which sheet the tables are  Can we do something about that   Function getAddress        myAddress   Replace Sheets  Sheet1   Range  Table1   address               myAddress     Sheet1    amp  myAddress  amp           getAddress   myAddress  End Function   Thanks

User · Answer

Hi recently looked into this and had issues referencing the named table (list object) within excel

if you place a suffix '$' on the table name all is well in the world

Sub testSQL()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    ' Declare variables
    strFile = ThisWorkbook.FullName

    ' construct connection string
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

    ' create connection and recordset objects
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    ' open connection
    cn.Open strCon

    ' construct SQL query
    strSQL = "SELECT * FROM [TableName$] where [ColumnHeader] = 'wibble';"

    ' execute SQL query
    rs.Open strSQL, cn

    Debug.Print rs.GetString

    ' close connection
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub

User · Answer

found this and it worked for me     strSQL    SELECT   FROM DataTable      Where DataTable is the Named range  How can I run SQL statements on a named range within an excel sheet

User · Answer

Just answering the second part of your question about getting the name of the sheet where a table is   Dim name as String  name   Range  Table1   Worksheet Name   Edit   To make things more clear  someone suggested that to use Range on a Sheet object  In this case  you need not  the Range where the table lives can be obtained using the table s name  this name is available throughout the book  So  calling Range alone works well

User · Answer

One thing you may be able to do is get the address of the dynamic named range  and use that as the input in your SQL string  Something like   Sheets  shtName   range  namedRangeName   Address   Which will spit out an address string  something like  A 1  A 8  Edit   As I said in my comment below  you can dynamically get the full address  including sheet name  and either use it directly or parse the sheet name for later use   ActiveWorkbook Names Item  namedRangeName   RefersToLocal   Which results in a string like  Sheet1  C 1  C 4  So for your code example above  your SQL statement could be  strRangeAddress   Mid ActiveWorkbook Names Item  namedRangeName   RefersToLocal 2   strSQL    SELECT   FROM  strRangeAddress

User · Answer

Public Function GetRange ByVal sListName As String  As String  Dim oListObject As ListObject Dim wb As Workbook Dim ws As Worksheet  Set wb   ThisWorkbook  For Each ws In wb Sheets     For Each oListObject In ws ListObjects         If oListObject Name   sListName Then             GetRange        amp  ws Name  amp       amp  Replace oListObject Range Address            amp              Exit Function         End If     Next oListObject Next ws   End Function   In your SQL use it like this  sSQL    Select   from    amp  GetRange  NameOfTable    amp

User · Answer

Building on Joan-Diego Rodriguez s routine with Jordi s approach and some of Jacek Kotowski s code - This function converts any table name for the active workbook into a usable address for SQL queries    Note to MikeL  Addition of    All   includes headings avoiding problems you reported   Function getAddress byVal sTableName as String  as String       With Range sTableName  amp     All            getAddress       amp   Parent Name  amp       amp   Address False  False   amp          End With  End Function

User · Answer

I am a beginner tinkering on somebody else s code so please be lenient and further correct my errors  I tried your code and played with the VBA help The following worked with me   Function currAddressTest dataRangeTest As Range  As String      currAddressTest   ActiveSheet Name  amp       amp  dataRangeTest Address False  False   End Function   When I select data source argument for my function  it is turned into Sheet1 A1 G3 format  If excel changes it to Table1  All  reference in my formula  the function still works properly   I then used it in your function  tried to play and add another argument to be injected to WHERE     Function SQL dataRange As Range  CritA As String   Dim cn As ADODB Connection Dim rs As ADODB Recordset Dim currAddress As String    currAddress   ActiveSheet Name  amp       amp  dataRange Address False  False   strFile   ThisWorkbook FullName strCon    Provider Microsoft ACE OLEDB 12 0 Data Source    amp  strFile    amp    Extended Properties   Excel 12 0 HDR Yes IMEX 1      Set cn   CreateObject  ADODB Connection   Set rs   CreateObject  ADODB Recordset    cn Open strCon   strSQL    SELECT   FROM     amp  currAddress  amp       amp              WHERE  A         amp  CritA  amp         amp              ORDER BY 1 ASC   rs Open strSQL  cn  SQL   rs GetString  End Function   Hope your function develops further  I find it very useful  Have a nice day

[sql] Performing SQL queries on an Excel Table within a Workbook with VBA Macro

Examples related to sql

Examples related to vba

Examples related to excel