Importing Excel Spreadsheet Automatically Without Conversion Error

JayB0730

Board Regular
Joined
Oct 22, 2014
Messages
64
Hello,

I'm trying to setup a macro that will allow me to upload a spreadsheet "ABC.xlsx" each time I run it; however, I keep getting a conversion error. The reason appears to be some data is not yet populated (blanks). Is there a way to import the data so everything is short text so everything matches?

TIA!
J
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,845
Office Version
365
Platform
Windows
Unfortunately, when you try to import Excel files into Access, it does not invoke the Import Wizard, and Access tries to "guess" at the data type and format of each field, and sometimes it guesses wrong. I believe it looks at the first 10 records of the file to try to determine those things.

Some methods for getting around that:
- Some people put a "dummy" record in at the top of the Excel file. In your case, this record would have a text entry for every field, so it would coerce Access to use the Text data type for every field.
- Alternatively, you could export the Excel file to a Tab-Delimited or CSV file. Then, when you go to import it into Access, the Import Wizard will be invoked, allowing YOU to determine the data type and format of each field.
- Instead of importing the Excel table, some people will link the table instead. Then will then use an Append Query to write the record from that linked table to their final table. Note that because of the potential data issues, the Append Query might need to do some field type conversion functions to get everything imported properly.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,845
Office Version
365
Platform
Windows
You are welcome.
 

Forum statistics

Threads
1,086,116
Messages
5,387,925
Members
402,089
Latest member
Exceliamus

Some videos you may like

This Week's Hot Topics

Top