Question Access 2007 not seeing named ranges from a Excel 2003 file when importing

[sychoangel]

This is a bizare one that I cant seem to find an answer for...

I have an excel 2003 file (users dont want to change it to excel07) that needs to be imported into an access 2007 database.

When I try importing it using from the quick access tool bar : External Data Tab > Import > Excel > specify the blah.xls file > and then select "Show Named Ranges"... none of the named ranges from the blah.xls file show!

However if I save it as an excel 2007 file and follow the same steps above, all the named ranges appear!

Also, with the same file - if i try creating a macro via the Macro option on the create tab, and then specify:
- Action : Transfer Spreadsheet
- transfer Type: Import
- Spreadsheet Type: Microsoft Excel 97 - Excel 2003 Workbook {have also tried with Excel Workbook}
- Table Name: blahblah
- File Name: c:\blah.xls
- Has Field Names: yes
- Range: BlahNames

it comes back with an error:
The Microsoft Office Access database engine could not find the object 'BlahNames'. Make sure the object exists and that you spell its name and path name correctly.

But if i change the macro to point to the version that was saved as an excel 2003 file (c:\blah.xlsx), it works perfectly fine!

I wish i could just use some VB code, but the person that does my job when i'm on leave will need to sometimes modify the file name & range specified, but they dont now code.

Has anyone got any ideas???

 

[sychoangel]

A week later & I'm still no closer to resolving this!!!

not happy

 

[vbaInet]

Use code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "blahblah", "c:\blah.xls", True, "BlahNames[COLOR=Red]![/COLOR]"

The main ingredient is the exclamation mark after the range name highlighted in red).

 

[sychoangel]

hi vbaInet...

That didnt work either. When running that code I get

Run-Time error '3125':
'BlahNames$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is now too long.

Have attached a moc up of the 2007 db/2003 ss if anyone wants to look


SychA

 

[vbaInet]

Your sheet name is called BlahMe not BlahName.

 

[sychoangel]

BlahName is the name of the named range I am trying to import.... as I only need to import specific columns and not the entire tab....

 

[vbaInet]

It doesn't like the reference of an entire column. If you create a named range for a few cells it works.

 

[sychoangel]

That is very interesting! Have just tried it & it works with cells selected in the named range & not columns.

Thanks for your help with this

 

[vbaInet]

You're welcome!