Link to home
Start Free TrialLog in
Avatar of Lawrence Barnes
Lawrence BarnesFlag for United States of America

asked on

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")
Thanks,

LVBarnes
Avatar of ioane
ioane
Flag of New Zealand image

I believe your only two options are to either use a date field in your Access table or reformat all dates before importing.
Avatar of Lawrence Barnes

ASKER

:(
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?
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.
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")
Tramtrak
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?

Helen
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.)
ASKER CERTIFIED SOLUTION
Avatar of ioane
ioane
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.

LVBarnes
Thank you Tramtrak!