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
– user9946692, Commented Jun 15, 2018 at 13:12
Have you done any debugging? Like does the SQL return a recordset, is new a sheet? Is the connection open? — Nathan_Sav
– Nathan_Sav, Commented Jun 15, 2018 at 13:14
How can I know if the connection is open? There is no change on my sheet "new" — user9946692
– user9946692, Commented Jun 15, 2018 at 13:18
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
– user9946692, Commented Jun 15, 2018 at 13:22

Ryan Wildry · Accepted Answer · 2018-06-15 13:59:30Z

1

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

edited Jun 15, 2018 at 13:59

Ryan Wildry

5,6971 gold badge17 silver badges36 bronze badges

answered Jun 15, 2018 at 13:28

jblood94

17.5k2 gold badges12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user9946692 Over a year ago

There is no error now, thanks a lot ! but nothing in the cell A1 of the "new" sheet. Maybe the request return nothing but there is a value in the cell B1 of the sheet "source"

jblood94 Over a year ago

@NCall Updated with a fix for HDR.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related