I am importing information from Excel to Access using the DoCmd.TransferSpreadsheet command from with VBA. The Excel information is being downloaded from SAP and has an issue that Access does not like when importing. On certain fields like vendor number, that is all numbers, sometimes shows up in Excel as a numeric value, but other times shows up with an error. The error being "number Stored as Text". Please see image. When Access imports this column, it will import the cells with no errors, but will not import the cells with the "number Stored as Text" error. Access gives the import error message of "Type Conversion Failure" regardless if the field in the Access table is setup as text or as a number.
Please Help, all help will be greatly appreciated.