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!

Maybe it won't make much of a difference, but I thought that custom functions had to be a Function (not a Sub). Also, at the end of the custom function you should have something like SQL = theAnswerToDisplayInCell But that might not relate to your issue. — Jake Bathman
– Jake Bathman, Commented Nov 3, 2013 at 17:43
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 [Sheet1$] or you could use SELECT * FROM MyNamedRange — user1274820
– user1274820, Commented Feb 25, 2022 at 15:47
And to add on to the above, is there a reason you can't just name your range in VBA and then run your code? You said "static named ranges", but you can simply adjust your named ranges dynamically if needed. — user1274820
– user1274820, Commented Feb 25, 2022 at 16:08

Jake Bathman · Accepted Answer · 2013-11-04 15:26:05Z

16

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

edited Nov 4, 2013 at 15:26

answered Nov 3, 2013 at 17:50

Jake Bathman

1,2773 gold badges18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Joan-Diego Rodriguez Over a year ago

Any chance we can get do it without the sheet name as an input? Man isn't is a real bummer that you can't just use the table's name with the ADODB connection? VBA is pretty weak, but I have to play by the rules in the corporate world :)

Jake Bathman Over a year ago

Assuming you know the name of the named range (which would defeat the purpose if you didn't) you could use this instead of .Address: ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal which spits out a string like =Sheet1!$C$1:$C$4

Thiago Souza Over a year ago

When I use strRangeAddress = Mid(ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal,2) and run the strSQL I get an error message Sheet1!$C$1:$C$4 is not a valid name. Can anyone help me?

user1274820 Over a year ago

@ThiagoSouza you can just use a named range in your query directly like this SELECT * FROM MyNamedRange

Johan Kreszner · Accepted Answer · 2015-10-20 11:20:31Z

9

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

answered Oct 20, 2015 at 11:20

Johan Kreszner

911 silver badge1 bronze badge

2 Comments

Avraham Zhurba Over a year ago

The main point in this answer is that address should look like this "Sheet1$A1:X10". 1) With $ between the sheet name and range, 2) WITHOUT $ in the range itself. This is missing in the first, most popular, answer.

Avraham Zhurba Over a year ago

BTW also very important to put range name into brackets [ ]. "Select * from [Sheet1$A1:A200]"

Craig Hatmaker · Accepted Answer · 2017-07-18 18:02:13Z

6

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

answered Jul 18, 2017 at 18:02

Craig Hatmaker

811 silver badge3 bronze badges

1 Comment

pheeper Over a year ago

This takes 12 lines of code from the answer above and refactors it down to 3. Job well done

Jacek Kotowski · Accepted Answer · 2015-03-05 13:55:21Z

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!

Jordi · Accepted Answer · 2016-11-22 20:25:39Z

2

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.

edited Nov 22, 2016 at 20:25

answered Jun 2, 2016 at 7:08

Jordi

2,6072 gold badges22 silver badges39 bronze badges

Comments

user1274820 · Accepted Answer · 2022-02-25 19:19:37Z

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

MikeL · Accepted Answer · 2017-04-10 12:43:27Z

0

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

answered Apr 10, 2017 at 12:43

MikeL

11

3 Comments

Bugs Over a year ago

OP is already appending $ to TableName...SELECT * FROM [Sheet1$A1:G3]". That isn't the problem here.

MikeL Over a year ago

but i'm not using a sheet name just the actual table name set within excel, without the $ the query complains that it cant find the object 'TableName' with the $ it returns data

MikeL Over a year ago

following on, i've had some mixed results on using actual column header names getting a 'no value given for one or more required parameters' message but using the index in format of [F1] for col 1 [F2] for col 2 etc works like a charm

Community · Accepted Answer · 2017-05-23 10:31:22Z

-3

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?

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Jan 25, 2017 at 20:02

fultonec

1

Collectives™ on Stack Overflow

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

8 Answers 8

4 Comments

2 Comments

1 Comment

Comments

Comments

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

4 Comments

2 Comments

1 Comment

Comments

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related