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

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

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

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

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    amp  strFile        amp    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

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

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

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

[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