Convert Excel Cell value formats B4 Access Table Insert

[misterpox]

Hello everyone,

I'm looking to see if anyone has any simple Access VB code to handle what I'm trying to do in my Access VB import command.

I have to import several Excel workbooks each containing sheets with 20K+ records into an existing Access table I've created that has the same layout as the spreadsheet column headings. The problem is that the Excel sheets were created from what looks like a simple text file dump into the sheet, making the cell values all having general format.

So for example, column A (field heading 'Event_Date') containing a date has the value, "06May2004", formatted as General. My Access table column 1 (named 'Event_Date') is set as a short date/time format. When I import the Excel sheet into the Access table, this field obviously bombs and doesn't import the values because of the data type differences.

The method I'm using to import the sheets is this (code summarized for illustration);

Option Compare Database
Option Explicit
Private Sub ImportEFXData_Click()

Dim mydbs As Database, myrst As Recordset
Dim filemssg, filetoopen, strfile As String

filemssg = "Please type the MSExcel file name you want to import."
filetoopen = InputBox(filemssg, "File Name Input")
DoCmd.TransferSpreadsheet acImport, 8, "ACCESS_TABLE_NAME", _
"C:\pathtoexcelworkbook\" & filetoopen, True, ""

Does anyone have a method in which I can loop through the field values in the Excel sheet and format them to the formats of the Access table prior to performing the transfer into the Access table? Or, is that not a very good way to do this, and some other method would be more efficient?

Thanks in advance for any help.

 

[Norie]

Why import directly into the table?

You could import into a 'temporary' table, with all the field types as text.

You could then create queries to deal with the rogue data and then import it to the main table.

 

[misterpox]

Norie,

Good suggestion. I was hoping though to be able to perform the data type conversions, "on the fly", and was curious as to whether that would be more efficient in respect to processing/import time. Anyone have any input on that aspect?

 

[Norie]

perform the data type conversions, "on the fly"

You could run the append queries via code and they could do the data conversion.