SQL request in VBA, referencing named range not working

Question

I'm trying to import data from an excel workbook with multiple sheets that by themself contain multiple tables of data. The goal is to reference a specific table in a sheet by its name and insert it into another excel sheet. It works if I only reference the sheet ([sheet$]) and if I use specific ranges (e.g. [sheet$A1:C2]) but [sheet$table] does not work for me.

The only additional package I use is "Microsoft ActiveX Data Objects 2.8 Library" ERROR:

Method 'Open' of Object '_Recordset' Failed

Code:

Sub GetData() Dim
    
    cn              As New ADODB.Connection
    Dim rs          As New ADODB.Recordset
    cn.ConnectionString = _
                          "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                          "Data        '';" & _
                          "Extended        'Excel 12.0 Xml;HDR=YES';"
    cn.Open
    rs.ActiveConnection = cn
    rs.Source = "SELECT * FROM [Sheet$Table]"
    rs.Open
    Sheet1.Range("A2").CopyFromRecordset rs
    rs.Close
    cn.Close
    
End Sub

I would appreciate some feedback for my problem.

which RDBMS are you using? MySQL,SQL Server,postgresql,..... . tag it correctly — RF1991, Apr 13 '22 at 09:13

score 0 · Answer 1 · answered Apr 13 '22 at 09:37

I guess you need to pass the sql correctly, currently the [Sheet&Table] is part of the string. Incorporate this in your code and see whether the MsgBox gives the correct sql:

dim sql = "SELECT * FROM " & [Sheet$Table]
MsgBox (sql)

With rs
    .ActiveConnection = cn
    .Open sql
    Sheet1.Range("A2").CopyFromRecordset rs   
End With

SQL request in VBA, referencing named range not working

1 Answers1