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!!

 

[JohanGduToit]

That seems to be the case.... I have just renamed the file to a .txt and did a successful manual (.txt) import into MS Access. Turns out that it's a 'tab delimited' file after all! Why would the sender name the file .xls if it's not??!! Grrr.

Anyway, I will use the Text Import Wizard and save the Import Specification as per your suggestion. I'm pretty sure it will work...

Thank you so much for pointing me in the right direction; much appreciated.

Will let you know shortly if succesfull.

 

[Joe4]

You are welcome.

Yeah, I have come across lots of people in my day who do weird things like this (many because they don't understand).
I once worked with a person who thought that all you needed to do to change an Excel file to a text file was to go to File Explorer and simply change the file extension (if only it were that easy!).

You may want to go back to them and let them know that they are not sending Excel files, and give it the right file extensions to save you the step from having to manually change it (and to educate them!).

 

[JohanGduToit]

You are welcome.

Yeah, I have come across lots of people in my day who do weird things like this (many because they don't understand).
I once worked with a person who thought that all you needed to do to change an Excel file to a text file was to go to File Explorer and simply change the file extension (if only it were that easy!).

You may want to go back to them and let them know that they are not sending Excel files, and give it the right file extensions to save you the step from having to manually change it (and to educate them!).

Will sure do!!

I now ran into another issue... thought it would be as simple as copying the file using:

FileCopy fWorkingPath & fName, fWorkingPath & fName & ".txt"

Which basically rename the file ABCD.xls to ABCD.xls.txt

Unfortunately that did not 'bluff' the Text Import Wizard... I get error:

Could you perhaps assist in how to properly copy or rename or change the file extension from .xls to .txt using vba?

Your help truly appreciated!!

 

[Joe4]

Try this:

Dim newName as String
newName = Left(fName, Len(fName)-3) & "txt"
FileCopy fWorkingPath & fName, fWorkingPath & newName

 

[JohanGduToit]

Try this:

Dim newName as String
newName = Left(fName, Len(fName)-3) & "txt"
FileCopy fWorkingPath & fName, fWorkingPath & newName

WORKS LIKE A CHARM!!

Text Import Wizard now allows the import....yay!

So I'll continue now to save the specification and make use of the TransferText method to get the data in...

You are a star...

PS : I was also looking at making use of the .SaveAs method; but since you're solution works I'm not going to bother going down that route.

 

[Joe4]

PS : I was also looking at making use of the .SaveAs method; but since you're solution works I'm not going to bother going down that route.

Yeah, I would not use that. You usually use "SaveAs" when trying to actually change the format (not just the extension), and it might depend on your original file have the approriate extension for the current file layout to start. Since you do not have that, I don't know if that would work out for you anyhow.

 

[JohanGduToit]

Yeah, I would not use that. You usually use "SaveAs" when trying to actually change the format (not just the extension), and it might depend on your original file have the approriate extension for the current file layout to start. Since you do not have that, I don't know if that would work out for you anyhow.

Perfect, thank you once again; I truly appreciate your assistance. I have marked your relevant entry as the solution. Enjoy the rest of your day/evening!

 

[Joe4]

You are welcome.
Glad I was able to help!