Importing delim file saved as .xls

[compsteve]

I'm trying to use VBA to automate the import of a file that has the extension
of .xls but is in fact a delimited file. When I open excel and try to open
the file the text import wizard opens. I have tried to do a transfertext
method but it gives a runtime error 31519. ANy suggestions would be greatly
appreciated.

 

[Stuart McCall]

I'm trying to use VBA to automate the import of a file that has the
extension
of .xls but is in fact a delimited file. When I open excel and try to
open
the file the text import wizard opens. I have tried to do a transfertext
method but it gives a runtime error 31519. ANy suggestions would be
greatly
appreciated.

You need to rename the file (or make a copy of it) with the extension .txt
or .csv

You can do this manually, or if you need it automated, try this (air code) :

FileCopy "MyPath\MyFile.xls", "MyPath\MyFile.txt"
DoCmd.TransferText acImportDelim, "MySpec", "MyTable", "MyPath\MyFile.txt"