Import Excel text (tab delimited) into Access using VBA

[vamoose]

Hello and good morning. I am having an issue trying to import a Excel 2003 file into Access 2003 using VBA. This Excel file is exported and saved from SAP as a text (tab delimited) file. When I attempt to import this file using Access and this VBA code:

DoCmd.TransferSpreadsheet acImport, 8, "SAP_Import", "\\Server2\Common\Materials\Winding.xls", True, ""

I receive the Run-time error '3274':
"External table is not in the expected format

But if I open the Excel Winding.xls file and save a copy in the latest Excel format it imports fine.

Does anyone have any suggestions on how to fix this issue they would like to share. Thank you.

 

[Golom]

It may have been an Excel file at some point but, by saving it as a tab-delimited text file, it no longer qualifies as a spreadsheet.

Look at the TransferText Method to read these kinds of files.

 

[vamoose]

Thank you, I never thought of that. What about opening the Winding.xls file (text, tab delimited), save it in the latest Excel format and then close the file using VBA before trying to import it with Access ?

 

[Golom]

Should work.

 

[vamoose]

Can someone point me in the correct VBA direction to accomplish this. Thank you.

 

[Golom]

You're already doing it

DoCmd.TransferSpreadsheet acImport, 8, "SAP_Import", "\\Server2\Common\Materials\Winding.xls", True, ""

If Winding.xls is now really an Excel spreadsheet then this should work.

 

[vamoose]

I meant how do I go about doing this with VBA:

open the Winding.xls file (text, tab delimited)
save it in the latest Excel format replacing the existing copy
close the file

Thanks

 

[djj55]

Do you really need to make it an Excel file? Why not just import from the text (tab delimited) file? Excel has a way of helping you out that causes no end of problems.
djj

 

[vamoose]

Ok, I used the TransferText as you suggested and it is working great now.

Next hurdle. In the text file, there is a column of data that contains a several numbers whose format looks like this: "1,720.00" which imports as nothing, an empty cell.

So my question is how to import this "1,720.00" from the text file into Access to appear as 1720

Thank you

 

[pcast01]

I also had that same error when importing Excel files. I had a workaround where I would have the excel file open and then that error would disappear. Since then I have changed file format. I change the Excel sheet to CSV file format then import using TransferText. It works better to import fields and I don't run into that error or have to open the file when I am importing. Strange....

Just thought I would share that.

CHAOS, PANIC, & DISORDER - my work here is done.

I currently use Access 2003.