1

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!

Improve this question
9
  • Did you try without the []? Commented Nov 7, 2016 at 19:08
  • I have, that produces a 'Syntax error in FROM clause' issue. I originally had it that way but added the brackets after I saw those referenced in other instructions on ADODB. Commented Nov 7, 2016 at 19:13
  • 1
    what about the ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name. Are you sure the ActiveWorkbook is the one you think it is and that is has a Purchases tab in it? Also try qualifing the full file path in the SQL statement. ` Commented Nov 7, 2016 at 19:21
  • I tried those by bringing that out to a MsgBox and can confirm that it is referencing the correct workbook. I also just tried hard-coding the path and it produced the same result. This one is a little bizarre because I've been able to produce SQL scripts like this successfully in the past but this one is deciding to complicate things! Commented Nov 7, 2016 at 19:26
  • Is there a reason to use ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name rather than just ActiveWorkbook.Fullname? Commented Nov 7, 2016 at 19:26

1 Answer 1

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Good one on the $. I knew there was a certain trick here that I was forgetting!
@ScottHoltzman - I had to google it to find the answer - I always just use a straight range name when specifying the source area.
This is perfect! I knew I was missing something simple. Thanks for helping out!
Quick question that I don't think I need another whole session for. How would I add a "HDR=No" portion to this appropriately. It keeps registering the second line of the table as the first and the first line as the header. Thanks!
@WSwanson - Using cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name & ";Extended Properties=""Excel 8.0;HDR=No;"";" I could get it to return the first row of the source range .... providing that row contains numeric values.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.