I am creating a procedure to import a spreadsheet into an Access 2013 for processing. Simple enough I've done it dozens of times.
I set up a table 'tblImport' to import the spreadsheet into with a column F1 - F12 to accept each field on the spreadsheet. Each column in this table is defined as text 50 with no 'Format' or 'InputMask' specified.
I use this code to import the spread into the tblImport
wkImportRange = "!A6:L8000"
DoCmd.TransferSpreadsheet acImport, _
I attached a file showing the pertinent columns in the spread. Everything works as it should except for most of the columns that may contain a date or a text description, usually the word 'Hold'.
The first column shown in the attached pic works just as I would expect. The data in that column is imported into column 'F7' in tblImport. In column F7 in the datasheet view I see rows with a date and rows with the word 'Hold' in them just as I would expect.
The remaining columns, which are all defined as text 50 just like column F7, don't import the same way. Only the dates import, the text 'Hold' never does. It throws a type conversion error. When I look at those columns in the imported table I only see dates, nothing is in
the rows that had the word 'Hold' in the spreadsheet cell.
I don't understand why column F7 would import properly and the others don't. They are all defined exactly the same in the table.
It seems like something in the Transferspreadsheet is causing these columns to be looked at a type 'Date', even though I have them defined as text.
I'm pretty sure I ran into this several years ago when importing a spreadsheet and the solution was to add a fairly short and simple piece of VBA code prior to importing the spreadsheet that changed the format of each cell in the import range to something like 'General'. The VBA then save the spreadsheet prior to the 'Transferspreadsheet' logic being executed.
Unfortunately I don't have Access to the VBA code I created to do the reformatting.
Does anyone have a solution to the problem?
Perhaps the pre-import reformatting is the only way to go. In that case can anyone provide an example of the code needed?