transfer spreadsheet - file doesn't contain data?

[pbuethe]

I am exporting an Excel file into a new table in Access using the TransferSpreadsheet method. The data starts out in a UNIX/Pick system. I had been exporting the data first to a .DBF (Dbase IV) file, then saving the file as Excel, then using TransferSpreadsheet to transfer the Excel file to the Access table.

Then I decided to try exporting directly from Pick to Excel and then to Access (TransferSpreadsheet apparently will not use Dbase files). The export from Pick to Excel worked, except that 2 fields (including the key field) lost leading zeroes that I wanted to preserve. Wanting to avoid having to open the Excel file, change the format, and re-save the file since that would not save any steps, I put a format code in the design of the table field. When I tried TransferSpreadsheet, I got the message 'the file "I:\URTracking\SURLOG.XLS" doesn't contain any data'. Checking the file, it does contain the data (except for the leading zeroes as I have described). I tried it again without the format code in the field design and got the same message.

What can be causing this message? Can it be related to the leading zeroes? Also, is there a way to either: automatically save the Dbase file as Excel? or:
automatically change the format of the Excel fields?
I am trying to do as much as possible automatically.

Thanks for your assistance.

 

[pbuethe]

I am not sure what Excel version it is. Maybe I can try doing the TransferSpreadsheet with a different version.

When I looked at Access help for TransferSpreadsheet, it did not list .DBF/Dbase files under spreadsheet types. It only lists different versions of Excel and Lotus. Can I use some version of Excel perhaps?

 

[pbuethe]

I tried TransferDatabase:

DoCmd.TransferDatabase acImport, "dBase IV", "\\Aimscluster\Aims\Aims\URTracking\SURLOG.DBF", acTable, "SURLOG", "tblSURLog", False

but it said:

"\\Aimscluster\Aims\Aims\URTracking\SURLOG.DBF" is not a valid path.

\\Aimscluster\Aims\Aims is mapped to the I: network drive, and I also got the "not a valid path" message when I had "I:\URTracking\SURLOG.DBF" for the path.

When I open the .DBF file, it appears as an Excel spreadsheet and says "SURLOG" on the worksheet tab. I am trying to import it to tblSURLog in Access. What am I doing wrong?

 

[pbuethe]

Thanks Pat, I read the Action entry and you were right. I changed the path to just the directory, and the source to the filename, and it worked. Thanks for your help!