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

JohanGduToit

Board Regular
Joined
Nov 12, 2021
Messages
89
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Morning All,

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

ImportError.jpg


Current Code:

VBA 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!!
 
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.
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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!).
 
Upvote 0
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:

VBA Code:
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:

1647004674695.png


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!!
 
Upvote 0
Try this:
VBA Code:
Dim newName as String
newName = Left(fName, Len(fName)-3) & "txt"
FileCopy fWorkingPath & fName, fWorkingPath & newName
 
Upvote 0
Try this:
VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
You are welcome.
Glad I was able to help!
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top