W Swanson W Swanson - 1 month ago 10
SQL Question

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!

Answer

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