Access Import of Excel Customized Date Formats into Text Field

Hello EE,

I am using Access 2007 to import an Excel file with dates into a table that is all text fields.  (The columns within the Excel files move around and there's a separate process that maps these using a schema.)

My problem is that a user may enter a date into an Excel date field as 11/01/09, but sometimes the users have date "formatted" to look like Nov-09 (mmm-yy).  After the import the Access table has the value of "Nov-09".  When converting this back to a date it becomes 11/09/2010.

How do I import the underlying date value from Excel  (40118) into the Access table's text field, if it exists, so that the proper date can be extracted from that field when that data is reformatted?  Hopefully it is just a transferspreadsheet switch.

I am using the following to import:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImportXLS", .Fields("FullFilePath"), False, .Fields("FileTabName")

Lawrence BarnesAsked:
Who is Participating?
ioanePlanning & Analytics ManagerCommented:
Hi lvbarnes,

I've created an example for you.

This procedure will take in any xl data file and import to a given  table in access.

Required references: (Go to Tools => References...)
Microsoft Office Object Library
Microsoft DAO 3.6 Object Library
Microsoft Excel 11.0 Object Library

Data must start in cell A1 (first row can be a header row.)
First row must NOT contain blank cells in column range.
First column must NOT contain blank cells in data range.

(Will only import fields up to the first blank cell in first column and data up to the first blank cell in the first row.)
'Imports xlData from given xl file to given access table
'Will replace any access table with given name
Public Sub ImportExcelData()
  Dim t As String 'table name
  Dim wb As Excel.Workbook
  Dim ws As Excel.Worksheet
  Dim r As Range
  Dim fd As FileDialog
  Dim fn As String 'filename
  Dim q As Integer
  Dim fns As String 'field names
  Dim fdcnt As Integer 'field count
  Dim dat As String 'data values
  Dim sql As String
  Dim i As Integer
  Dim vnt As Variant
  DoCmd.SetWarnings False
  On Error GoTo Err_ImportExcelData
  'Get data filename
  Set fd = FileDialog(msoFileDialogFilePicker)
  With fd
    .AllowMultiSelect = False
    .Filters.Add "Excel Files", "*.xls"
    If .Show Then
      fn = .SelectedItems(1) 'File to import.
      Exit Sub
    End If
  End With
  Set wb = GetObject(fn)
  Set ws = wb.Sheets(1)
  'Check if file contains header row
  q = MsgBox("First row contains field names?", vbYesNo + vbQuestion)
  'Get number of fields and field names if header row exists
  fdcnt = 0
  For Each r In ws.Range(ws.Range("A1"), ws.Range("A1").End(xlToRight))
    If q = vbYes Then
      fns = fns & "," & r
    End If
    fdcnt = fdcnt + 1
  Next r
  fns = Mid(fns, 2) 'remove preceding comma
  'Create data table
  t = InputBox("Enter table name to import to." & Chr(13) & _
                "Note, if table already exists it will be replaced." & Chr(13) & _
                "(I.e. any existing information will be deleted.)")
  On Error Resume Next
  DoCmd.RunSQL "DROP TABLE " & t & ""
  On Error GoTo Err_ImportExcelData
  sql = "CREATE TABLE " & t & " ("
  If q = vbYes Then
    vnt = Split(fns, ",")
    For i = 0 To UBound(vnt)
      vnt(i) = vnt(i) & " text"
    Next i
    sql = sql & Join(vnt, ",") & ")"
    For i = 1 To fdcnt
      fns = fns & "," & "[Field" & i & "] text"
    Next i
    sql = sql & Mid(fns, 2) & ")"
  End If
  DoCmd.RunSQL sql
  'Add data values
  For Each r In ws.Range(ws.Range(IIf(q = vbYes, "A2", "A1")), ws.Range(IIf(q = vbYes, "A2", "A1")).End(xlDown))
    dat = ""
    For i = 0 To fdcnt - 1
      dat = dat & ",'" & r.Offset(0, i).Value & "'"
    Next i
    dat = Mid(dat, 2) 'remove preceding comma
    sql = "INSERT INTO " & t & " " & IIf(q = vbYes, "(" & fns & ")", "") & " VALUES (" & dat & ")"
    DoCmd.RunSQL sql
  Next r
  If Not wb Is Nothing Then
    wb.Application.DisplayAlerts = False
  End If
  Set wb = Nothing
  Set ws = Nothing
Exit Sub
  MsgBox Err.Description
  Resume Done
End Sub

Open in new window

ioanePlanning & Analytics ManagerCommented:
I believe your only two options are to either use a date field in your Access table or reformat all dates before importing.
Lawrence BarnesAuthor Commented:
In Excel there is the information that the user enters, and then there is how the information is displayed per direction by the users.  I think of this is two layers of data.  So there is no way to specify which layer to import?  When Access imports an Excel file into text fields it is basically a "print" to Access only?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ioanePlanning & Analytics ManagerCommented:
IMHO that's correct, there are several different fields that information can be stored in within a range object in Excel. (eg. Formula, Value, Text, etc.) What I am assuming is MS's way of simplifying things is to export the displayed information only.

If you want to export/import more detail then you would have to do it manually with vba code. i.e. write a procedure to get each row of information and use an sql append statement to put it into your table. In this case, you might as well just use vba to format the date columns and use the transferspreadsheet function. This would be relatively simpler.

If it was me, I'd probably save the spreadsheet as a temp file using the Workbook.SaveAs function to get around issues with changing other people's files and read-only permissions, reformat the date columns (and any other fields as required), import to Access, and then delete the file.
Helen FeddemaCommented:
I would just import the dates, and if you need a Text date field, use the Format function in a query calculated field to convert the dates to text strings, for example TextDate: Format(dteMeeting, "dd-mmm-yyyy")
Lawrence BarnesAuthor Commented:
So...the Excel spreadsheet could be imported line by line into the text fields that I need and the line-by-line import would bring in the data entry layer of Excel vs the formatted layer?  Do you know where I could find an example of that vba line-by-line code?

Unfortunately that approach won't work as the user's entered 8/09/2009, which is then formatted to look like Aug-09 in Excel, will be imported as Aug-09.  When CDate or Format is used it will be converted to 10-Aug-2010 (with the incorrect year using your Format example.)
ioanePlanning & Analytics ManagerCommented:
Sorry, this note is wrong:
(Will only import fields up to the first blank cell in first column and data up to the first blank cell in the first row.)

Should be:
(Will only import fields up to the first blank cell in first row and data up to the first blank cell in the first column.)

**words "column" and "row" back to front.
Lawrence BarnesAuthor Commented:
Thank you Tramtrak!
I've tested it and it is returning the user entered data and ignoring the custom formatted values.

For anyone else following this thread the references above are for Access 2003, there are slight naming variations with Access 2007.

Lawrence BarnesAuthor Commented:
Thank you Tramtrak!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.