Importing Excel Spreadsheet Automatically Without Conversion Error

[JayB0730]

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]

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.

 

[JayB0730]

This is helpful, thank you!

 

[Joe4]

You are welcome.