Problem with named Range in ADO extract from Closed Excel File

[Dave Bash]

I have used the ADO method to extract cell data from a closed Excel
file in the past successfully but in my current efforts I am using a
named range which does not appear on the first sheet (putting in a
simple a1:d10 as the range does work but grabs front sheet). The
argument in the call is like this:

GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls",
"MyDataRange", Range ("B3"), True

where MyDataRange is a range on the 10th sheet named "Projected" and
has a first row which consists of AccountName,and then dates all the
way across.

The code errors out to the error handler at the line:
Set rs = dbConnection.Execute("[" & SourceRange & "]")


this is the source for the code - and the platform is xl2000 on
win2000.

http://www.erlandsendata.no/english/vba/adodao/importwbado.htm

Thanks

 

[onedaywhen]

Try calling your function like this:

GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", _
"Projected$MyDataRange", Range ("B3"), True