CODEWITHSUNDEEP
sqlvbaexceladodb
W Swanson
W Swanson

Reputation: 37

Error Querying a table using ADODB with VBA in Excel

I am attempting to use SQL to query a table (Purchases) in Excel. However, I get an error when the script below is run.

The tableaddress variable produces Purchases!$A$2:$F$1200 which is the range of the table "Purchases".

The SQL query that is produced is:

Select * From [Purchases!$A$2:$F$1200]

The current VBA itself is as shown below:

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strSQL As String
    Dim tableAddress As String

    Set cn = New ADODB.Connection
    cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name

    cn.Open
    tableAddress = "Purchases!" & Range("Purchases").Address
    strSQL = "Select * From [" & tableAddress & "]"
    rs.Open strSQL, cn
    cn.Close

However, when executing I get the error below...

[Microsoft][ODBC Excel Driver] 'Purchases!$A$2:$F$1200' is not a valid name.
Make sure that it does not include invalid characters or punctuation and that it is no too long.

I see the apostrophes in the error but am not sure if that's the punctuation being referred to or how to get rid of it if so.

Thanks!

Upvotes: 1

Views: 782

Answers (1)

YowE3K
YowE3K

Reputation: 23974

The following code should work:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim tableAddress As String

Set cn = New ADODB.Connection
cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name

cn.Open
tableAddress = "Purchases$" & Range("Purchases").Address(False, False)
strSQL = "Select * From [" & tableAddress & "]"
rs.Open strSQL, cn
cn.Close

Upvotes: 1

Related Questions