Unable to Import Excel97-2003 Worksheet into Access (VBA)

[JohanGduToit]

Morning All,

I receive an Error '3274' when attempting to import an Excel 97-2003 (.xls) file into Access.

Current Code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "CRAZY_RAW", fWorkingPath & fName, False

I would have thought that acSpreadsheetTypeExcel8 would cater for the fact that the file is a Excel 97-2003 (.xls) file; but it does not work.

Any help would be greatly appreciated!!

 

[Joe4]

What exactly are the values of fWorkingPath & fName?

 

[JohanGduToit]

D:\<Path>\<FileName.xls>

 

[JohanGduToit]

When trying to open the file with Office Professional Plus 2013 I get this message:

 

[Joe4]

Is "Temp" supposed to be the last subfolder (just making sure that you aren't missing a "/", as people sometimes forget to put it after the last file path and before the file name.

Are you able to open this file directly in Excel? If not, I doubt that you can import it into Access, as that would mean there is probably something wrong with it.
Where is this file being created?
Does it contain VBA code?

 

[JohanGduToit]

When opening the "xls" file with Notepad, it looks like this....so I'm thinking that the file may be in another format; but named with an .xls extension (for whatever reason?)

 

[JohanGduToit]

Is "Temp" supposed to be the last subfolder (just making sure that you aren't missing a "/", as people sometimes forget to put it after the last file path and before the file name.

Are you able to open this file directly in Excel? If not, I doubt that you can import it into Access, as that would mean there is probably something wrong with it.
Where is this file being created?
Does it contain VBA code?

Correct, "Temp" is the last folder and I do have the "\" before the filename.

When opening the file in Excel, I get:

 

[JohanGduToit]

...and when I then select <Yes> it opens as:

 

[JohanGduToit]

The file do not contain any vb code and gets sent to us from a supplier. I then reformat the original file (by removing top row and some columns and save the file. I then try to import it into Access and it's then that I get the error.

 

[Joe4]

When opening the "xls" file with Notepad, it looks like this....so I'm thinking that the file may be in another format; but named with an .xls extension (for whatever reason?)

View attachment 59800

If you can open the file in NotePad, and it looks like that, it is definitely NOT an Excel file. An Excel file would not look like that. There would be a whole bunch of formatting code, and it would be pretty much non-readable in NotePad.

What you have there is a straight text file. From the looks of it, it is probably a tab-delimited text file. You would use the "TransferText" command on a text file, not "TransferSpreadsheet". You may need to change the extention from "xls" to "txt".

I would first manually import the file once using the Text Import Wizard, and then saving the Import Specification. Then you can use that Import Specification in your "TransferText" command to tell Access how the file is to be parsed.