Get Data form closed Excel workbook with ADODB

[Kappes105]

Hello everyone

I am trying to read data from a closed workbook. I would like to use dynamic range names. I define these with the name manager.

My problem is as follows:

If I set the name to fixed =Sheet1!$A$1:$A$3, then access to the datarange works perfectly.

However, if I create the name dynamically =OFFSET(ShLookup!$B$1,0,0,COUNTA(shLookup!$B:$B),1), I get the error message that the object “Range name” cannot be found.

My select looks like this
' SQL query to retrieve the named range
strSQL = “select * from [” & NamedRange & “]”

Does anyone have any ideas on how I can make the access so that the dynamically named range is used?

Translated with DeepL.com (free version)

 

[Jeffrey Mahoney]

Getting data using ADO won't let you reference dynamic named ranges. It is best just to grab a huge section of the data and parse it in the receiving workbook. So if your table is dynamically range A1:J1001, then simply get all the data from A1:X1000000. Then use some code to figure out the column count and row count after you paste it.

You could also created a named range in your source file that encompasses more rows than you'll ever use.

 

[Kappes105]

Many thanks for the information