Excel VBA to get data from a file without opening it

I have a code to extract data from a closed worksheet, but I keep getting an error (See the attached pictureerror.PNG)

So what I'm trying to do is open a file whose name keeps changing. I have the path to the file (including the file name) in an Instructions sheet in cell C29.
The name of the sheet I'm trying to open also changes, it's called "C18 Summary", so the value 18 is defined in the instructions sheet in Cell C23. And form this sheet the range I'm trying to copy is A61:R66.

I think the error is, and correct me if I'm wrong, but the file I trying to open in Cell 29 is not actually being opened?

How did I fix this??

Please Login or Register  to view this content.

I'd recommend you upload sample workbooks (source and output files).

To upload use "Go Advanced" button and follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.

What the error is saying, basically file name/table does not exist. Meaning you have syntax for the table/range wrong and/or something else wrong in connection string.

You had unnecessary space after " Summary".

Modify your code to reflect below and it will work.

Please Login or Register  to view this content.

You can't.

ADO (or any other query tool reading from closed workbook) is meant to bring in data set, not formatting etc. Though you can specify data type, you can't control font/cell format etc.