469,327 Members | 1,226 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,327 developers. It's quick & easy.

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

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!!
Jan 24 '11 #1
11 9464
ADezii
8,800 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. '************* Define Your Own Constants *************
  2. Const conFILE_PATH As String = "C:\Test\Test.xls"
  3. Const conRANGE_NAME As String = "Test_Range"
  4. Const conTABLE_NAME As String = "Table1"
  5. '*****************************************************
  6.  
  7. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, conFILE_PATH, True, conRANGE_NAME
Jan 25 '11 #2
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
Jan 25 '11 #3
ADezii
8,800 Expert 8TB
  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?
Jan 25 '11 #4
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.
Jan 25 '11 #5
ADezii
8,800 Expert 8TB
.zip the Spreadsheet, then Upload it. I'll have a look later today.
Jan 25 '11 #6
Thanks for staying with me on this. BTW, I'm using Office 2003, but with Office 2007 converters loaded.
Jan 25 '11 #7
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!
Jan 25 '11 #8
ADezii
8,800 Expert 8TB
Very strange behavior, Laurel.
Jan 25 '11 #9
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!
Jan 25 '11 #10
ADezii
8,800 Expert 8TB
That's why the only Access Version that I use is 2003! (LOL).
Jan 25 '11 #11
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.
Jan 25 '11 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Schultz | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by listenups61195 | last post: by
reply views Thread by Purva khokhar | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.