Excel 2007 : External Links to Single Tab Source Files

Greetings All,

I am just migrating to Excel 2007 and discovered a problem with links to single tab files in converting from 2003.... 2007 deletes the tab names and generates a #Ref error!!

I use data draw tabs to pull selected data from system generated/exported excel files. Most of these have single tabs with a tab name the same as the file name (from the system report name usually). HOWEVER, if the file name is longer than the tab name, 2007 blows up.

I actually had a file with 2 source files, one where the file name fit on the tab and one where it was a single character short. 2007 linked to the first and #Ref'd the second.

The problem seems to be an undocumented format for external links to a single tab sheet. While the standard format is:

'[file name.xls]tab name'!A1

The undocumented, truncated format is:

'file name.xls'!A1

This format is dynamic.. I tested a fresh link in a dummy pair of files and if there is only a single tab in the source, you get one format and if you add a tab, the format changes automatically.

I was able to repair this first simple target file by using Find and Replace to swap #Ref for the Tab Name. When I save the file it stuck, even if I copy over the source file, which is how I swap out data sources daily & weekly. It even re-inserted the tab name if I added an extra tab to the source.

HOWEVER, I have about 50 report generator files that use as many as 6-8 source files each, all machine generated and I really don't want to have to Find & Replace my way through each one.

Has anyone found anything on this error?

THANKS!!

Dean