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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. 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
55,950
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,959
Messages
5,599,056
Members
414,281
Latest member
Engjamal2021

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
Top