Excel, VBA, SQL - Retrieve data from another workbook / Error

Question

I want to retrieve data from a closed workbook. My code is

fileName = "the path\test.xlsx"
With CreateObject("ADODB.Connection")
    .CommandTimeout = 500
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
        & fileName & ";" & "Extended Properties=""Excel 12.0;HDR=YES;Readonly=true"";"
    .Open
      ThisWorkbooks.Worksheets("new").Range("A1").CopyFromRecordset .Execute("select * from [source$B1]")
    .Close
End With

I have some error, 424:object required, object doesn't found... I think it is a syntax problem. The purpose is to retrieve data from cells and put in the other sheet/workbook thanks for your help

ThisWorkbooks.Worksheets("new").Range("A1").CopyFromRecordset .Execute("select * from [source$B1]") — user9946692
Have you done any debugging? Like does the SQL return a recordset, is new a sheet? Is the connection open? — Nathan_Sav
How can I know if the connection is open? There is no change on my sheet "new" — user9946692
It is the right way to retrieve the value of the cell B1 (closed workbook, sheet "source") and put it in the cell A1 (current workbook, sheet "new") — user9946692

jblood94 jblood94 · Accepted Answer · 2018-06-15T13:28:13

Three things:

Unless you defined "ThisWorkbooks" somewhere, I think it should be "ThisWorkbook".
Using this method, I have only ever been able to fetch ranges that contain ":" in them, so change source$B1 to source$B1:B1.

Since you only want one cell, you should turn the HDR (header) option off.

fileName = "the path\test.xlsx"

With CreateObject("ADODB.Connection")
    .CommandTimeout = 500
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
    & fileName & ";" & "Extended Properties=""Excel 12.0;HDR=No;Readonly=true"";"
    .Open
    ThisWorkbook.Worksheets("new").Range("A1").CopyFromRecordset 
    .Execute("select * from [source$B1:B1]")
    .Close
End With

Excel, VBA, SQL - Retrieve data from another workbook / Error

1 Answers