DoCmd.TransferSpreadsheet not working with named Range

[Margi]

I want to import the same range from multiple worksheets in a workbook into
Access tables. I can get the local range to import just fine, using either a
literal or a variable, so long as I use cell notation: eg,
DoCmd.TransferSpreadsheet acImport acSpreadsheetTypeExcel97,
"tblTempXLProjects", _
szPath, False, "116!C2:AP10"
or a variable whose value is set to 116!C2:AP10.

When I substitute XLProjects (the named range) in either the literal or the
variable, it doesn't work. I've tried using !, :, $, etc.

I'm using MS Access 2002 in an Access 2000 DB.

Any help would be appreciated. I have thought of using other object
language to get the cell values for the ranges, but if I can do this taking
advantage of the simplicity of TransferSpreadsheet, it would simplify things.

Thanks!

 

[Paul Herber]

You'll need to ask in an Access or Excel group, here is for Visio.

 

[Margi]

Sorry. The title of the group is Developer VBA, which I thought described
what I was doing pretty well. I should have known - you can find anything
in/about Microsoft if you already know where it is!