We help IT Professionals succeed at work.
Get Started

Transferspreadsheet formatting issues

Last Modified: 2015-08-31
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, _
                          acSpreadsheetTypeExcel8, _
                          inputTable, _
                          passedSpreadsheetNameAndLoc, _
                          False, _

Open in new window

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?
Watch Question
Top Expert 2016
This problem has been solved!
Unlock 1 Answer and 22 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE