How to import an Excel 2003 file into an Access 2003 table for a field that has both date and character information?
I am trying to import the following attached Excel 2003 file into my Access 2003 application.
I have defined all the fields in the Access table that the Excel file is being imported to
with field type TEXT and field size of 255.
The values in column K are not all being picked up. Do you know what change(s) I need
to make to pull in all values for column K. The user may input a combination of
dates and text combined into this field. It is a comment field. It seems that if the value in the Excel file for column K is purely a date, that this value is imported into the Access table but if the value for column K is a combination of a date and text information, that value is not imported into the Access table.
I use the following command to import the Excel file:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Bank Credits (A)", filenm, False, "Bank Credits (A)!A:N"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Bank Debits (B)", filenm, False, "Bank Debits (B)!A:N"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Book Debits (C)", filenm, False, "Book Debits (C)!A:N"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Book Credits (D)", filenm, False, "Book Credits (D)!A:N"
666-02-15-2013.xls
I have defined all the fields in the Access table that the Excel file is being imported to
with field type TEXT and field size of 255.
The values in column K are not all being picked up. Do you know what change(s) I need
to make to pull in all values for column K. The user may input a combination of
dates and text combined into this field. It is a comment field. It seems that if the value in the Excel file for column K is purely a date, that this value is imported into the Access table but if the value for column K is a combination of a date and text information, that value is not imported into the Access table.
I use the following command to import the Excel file:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Bank Credits (A)", filenm, False, "Bank Credits (A)!A:N"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Bank Debits (B)", filenm, False, "Bank Debits (B)!A:N"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Book Debits (C)", filenm, False, "Book Debits (C)!A:N"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Book Credits (D)", filenm, False, "Book Credits (D)!A:N"
666-02-15-2013.xls
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Attached is a stripped down version of my Access database. To get into the application you could hold down the shift key and double click on the Access mdb file. Once you succesfully open the application, you can go to the form frmImport. When you double click on the BROWSE command button and open the Excel file I attached in my original post above and then double click on the IMPORT command button to import the Excel file into the Acccess tables, I think you will see that not all of the cell values in column K are imported into the Access tables.
Copy-of-ReportsDB1.mdb
Copy-of-ReportsDB1.mdb
ASKER
For ex:
ssql = "insert into tblOpenItems (bank, office, checknum,refno, [trans date], type,[process date], agedays, t, description, amount, manual,rptid, [report name], footnote,responsibility) "
ssql1 = " SELECT '" & bankNum & "',f7,f8,f10, cdate(f2),f4,cdate(f1), GetNumberOfWorkDays(f1, now()), f3,f5,f9, 'I',( select rptid from tblbanks where [bank code]='" & bankNum & "'),( select [report name] from tblbanks where [bank code]='" & bankNum & "'), f11,f14 FROM [Bank Credits (A)] WHERE (IsNumeric([f9])<>False) AND (F1 Is Not Null);"
rst.Open ssql & ssql1, con, 2, 2