HELP! Problem importing Excelsheet due to length of tab name

[kuipers78]

Hello,

I need to import a specific tabsheet from an Excel file into my DB. I'm using the following method:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, 
"[Name_of_Access_table]", "[Location_of_Excel_file]", False, 
"[[COLOR="Red"]Name_of_tab[/COLOR]]![Columns]"

This works fine, but only when the length of the tab name (including spaces) is less than 31 characters. And guess what, it's not...

Of course I can shorten the name in the Excel file, but this is not desirable. I prefer not to alter the source file.

So, is there a way to import the file (using this method) while the length of the tab name is more than 30 characters???

Any help will be greatly appreciated! Thanks!

 

[KenHigg]

What happens when the name is more than 31? What kind of error message are you getting?

 

[kuipers78]

I'm sorry, forgot to mention it. The error message is (when using more than 30 characters):

Error 3011 during execution:

The Microsoft Jet-database-engine can't find the object [Name_of_tab]$[Columns]. Make sure the object exists and the name and location are spelled correctly.

 

[DJkarl]

This seems to be an undocumented limit of Access Imports and Exports in regards to Excel Worksheets. I encountered the reverse of this last week, I was exporting using the OutputTo method and any queries that had a name > 31 characters were getting truncated to 31 characters. I resolved this by creating an Excel Application Object and worked with the workbook directly rather than rely on the Docmd method.

 

[KenHigg]

That's what I was thinking -