CODEWITHSUNDEEP
ms-accessms-access-2007vbams-access-2010
Matt Hall
Matt Hall

Reputation: 2412

Import Errors from Excel to Access Using DoCmd.TransferSpreadsheet acImport Method

I'm getting some import errors when importing data from Excel in to Access.

I'm using the DoCmd.TransferSpreadsheet acImport, ... method to do the import.

The column of data that is failing contains a mixture of number-only entries and strings. It's the string entries that are failing to import.

Is there something I can do to the data in this column of the Excel spreadsheet to ensure it gets across to Access in its entirety?

Upvotes: 0

Views: 3998

Answers (2)

Matt Hall
Matt Hall

Reputation: 2412

I found something approaching a workaround:

  1. Sort your offending column in Excel so that your numerics appear as the top-most group and your alphanumerics are at the bottom.

  2. Highlight all your numerics now grouped in the column

  3. Go to Data > Text to Columns

  4. Wizard Page 1: Select "Delimited"

  5. Wizard Page 2: Tick "Tab"

  6. Wizard Page 3: Select "Text" and finish

Numbers will be stored as text with the leading apostrophe and should import ok in Access

Source

There's probably a better way of doing this though if anyone wants to chip-in.

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123829

While your Excel.Application code from your previous question is in there "counting rows" it could also inspect the cell for that column in the first data row. If it is numeric, your code could glue an apostrophe (') at the beginning to force it to be a label, and then save the Excel file. Then, when Access' TransferSpreadsheet method looks at the first row it will decide that the column is text, not numeric.

Upvotes: 1

Related Questions