How to import data from named ranges in Excel 2003 into Access?

[Laurel Eppstein]

I have no hair left with this issue! I'm simply trying to import data from named ranges in Excel 2003, into tables of the same name in Access 2003. I have found that unless I have the spreadsheet from which I want to import, OPEN, the named ranges cannot be found. I can't even see them trying to manually import the data, unless the spreadsheet is open!

I don't want the spreadsheet open because that causes an instance of Excel to remain active, as viewed in Windows Task Manager/Processes (another issue, but what started this whole long nightmare).

I have used variations of this code many times over the years, but now it doesn't work. I have installed Office 2007 converters, which I'm wondering if is causing this issue. Here's the line of code that fails:
[DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tmp" & tbl, FileName, True, rng]

tbl = a string that is the name of a table in Access, and also a named range in Excel. "tmp" & tbl is a temporary file which holds the import. All field names are identical in the spreadsheet and the Access tables.
Filename is the path and name of the spreadsheet.

Please help!!

[ADezii]

The following Code will Import a Named Range, in an Excel Spreadsheet, into an existing Access Table. The Spreadsheet need not be 'Open' for this process to occur successfully.

'************* Define Your Own Constants *************
Const conFILE_PATH As String = "C:\Test\Test.xls"
Const conRANGE_NAME As String = "Test_Range"
Const conTABLE_NAME As String = "Table1"
'*****************************************************
 
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, conFILE_PATH, True, conRANGE_NAME

[Laurel Eppstein]

ADezii, that code is no different than what I had posted. I get the same error running it as I did with mine. Yet when I have the spreadsheet open, it runs just fine. THAT is the problem!

Thanks for trying,
Laurel

[ADezii]

1. What is the Range Assignment for the Variable rng?
2. Does the 1st Row of the Spreadsheet have Field Names?
3. Out of curiosity, can you Upload the Spreadsheet stripped of any confidential information?

[Laurel Eppstein]

1. Range assignment is a string, identical to the table to which it will ultimately be appending in Access. An example is "tblFacility". The transferspreadsheet method creates a temporary table of the same name, but with the 'tmp' prefix.
2. The first row of each named range has field names
3. I would upload the spreadsheet except that I see no way to do so: the allowed extensions are picture-types or docs or txt.

[ADezii]

.zip the Spreadsheet, then Upload it. I'll have a look later today.

[Laurel Eppstein]

Thanks for staying with me on this. BTW, I'm using Office 2003, but with Office 2007 converters loaded.

[Laurel Eppstein]

Another piece of info: this works seamlessly in Office 2010, if I save the file first as a .xlsm type, and then specify the spreadsheet type as 12. However, if I try to do the import in Access 2010 of spreadsheet type 8 or 9, with the older version of the Excel spreadsheet, it can't find the named range!

[ADezii]

Very strange behavior, Laurel.

[Laurel Eppstein]

It seems as though it's an Access problem. When I imported my entire database into Access 2010, the same thing occurred: could see named ranges of .xlsm files, but not .xls files. For various backward-compatibility issues, I do not choose to develop in 2010. ARGHH!

[ADezii]

That's why the only Access Version that I use is 2003! (LOL).

[Laurel Eppstein]

Unfortunately, the Rest of the World is moving on, including my coworkers and associates. Therefore the need for up-converters, which I fear have ruined Office 2003.