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

Just wanted to add here that you can just make your range a named range and use it in your query (or an entire sheet if you have a single table per sheet).

So you can use:

SELECT * FROM MyNamedRange

OR

SELECT * FROM [Sheet1$]

A lot of these answers seem to go to the effort of parsing out addresses when what I've done historically is just setup all my tables as named ranges and refer to them directly in the query (makes it a lot cleaner too).

In one of my more complicated queries below, all of the ranges I refer to are named ranges:

SELECT Unit_Type AS [Unit Type], COUNT(*) AS [Number of Units], SQFT, Deposit AS [Deposit{$}], Rent AS [Base Rent{$}], SUM(RENT) AS [Base Rent Total{$}], SUM(MR) AS [Market Rent Total{$}] FROM
(
    SELECT f1.Unit_Code, Unit_Type, SQFT, Rent, Deposit, SWITCH(MR IS NULL,Rent,MR IS NOT NULL,MR) AS MR FROM
    (   
        SELECT t2.Unit_Code, t2.Unit_Type, SQFT, Rent, Deposit FROM
        (
            SELECT DISTINCT Unit_Code, Unit_Type
            FROM CommUnits
            WHERE Unit_Code NOT LIKE "%WAIT%" AND Exclude=0
        ) t2
        LEFT JOIN
        (
            SELECT UnitType_Code, SQFT, Rent, Deposit
            FROM ResUnitTypes
        ) t1 ON (t1.UnitType_Code = t2.Unit_Type)
    ) f1
    LEFT JOIN
    (
        SELECT Unit_Code, SUM(Current_Charge) AS MR
        FROM ResUnitAmenities
        WHERE Unit_Code NOT LIKE "%WAIT%"
        GROUP BY Unit_Code
    ) f2 on (f1.Unit_Code = f2.Unit_Code)
)
GROUP BY Unit_Type, SQFT, Deposit, Rent

Answer 2

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 & "[#All]")
        getAddress= "[" & .Parent.Name & "$" & .Address(False, False) & "]"
    End With

End Function

Answer 3

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

Answer 4

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?

Answer 5

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.

Answer 6

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") & ""

Answer 7

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 & "$" & 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 & "$" & dataRange.Address(False, False)

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 [" & currAddress & "]" & _
         "WHERE [A] =  '" & CritA & "'  " & _
         "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!

Answer 8

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