External Table not in expected Format

[merry_fay]

Hi,

I'm setting up some vba to import a series of tables. They're saved as excel 97-2003 files but are a bit weird.

When I open them in excel, they say it's in a 'different format than that specified by the file extension' & so when access runs the vba, I get the 'external table not in expected format....' message.

I can open the excel file, save it & it will then import fine, but that's a pain to have to do every month for lots & lots of files.

The data is downloaded into excel from a currently unidentified system & the chance of getting the supplier to change them is slim, so is there a way to do a workaround -save excel before importing via access, different kind of excel imports etc to get it to work?

I'm current using acSpreadsheetTypeExcel9 as the excel type.

Thanks
merry_fay

 

[merry_fay]

Does nobody have any suggestions for this then?

Thanks

 

[PeterF]

Most likely the files are actually comma separated (csv) with a .xls extension.
But that's a shot in the dark. Without more information on the program that creates the files or the actual file I don't think anyone can answer your question.

 

[gemma-the-husky]

what is the full name of the file on your disk. you may need to change the view options on the folders to display extensions for known file types.