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=" & strFile _ & ";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$" & myAddress & "]"      getAddress = myAddress  End Function 

Thanks!

Answer 1

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]" 

Answer 2

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 = "[" & ws.Name & "$" & Replace(oListObject.Range.Address, "$", "") & "]"         Exit Function         End If     Next oListObject Next ws   End Function 

In your SQL use it like this

sSQL = "Select * from " & GetRange("NameOfTable") & ""