Importing Excel sheet into access

Question

I am trying to import data from Excel sheet into access table. I am trying to automate data manipulation procedure. So i can read the data into access by this code:

Private Sub ctlOpen_Click()
    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = False

  If f.Show Then
     strExcelPath = f.SelectedItems(1)
  End If
  Call DoCmd.TransferSpreadsheet(acImport, _
    acSpreadsheetTypeExcel8, "InitialTable", strExcelPath, _
    True)
End Sub

But i get an error. It creates another table called import errors stating 'Type Conversion Failure' followed by column name and row number.

The problem is that there is a Column on my excel sheet that is called ETA and it has a date. Majority of the fields in the column would have month/date like ('1/20' or 1/17') but some of fields have codes like 'BAD' or 'CP'. Part of my data manipulation would be changing everything that is 'BAD' to one week from today, and different formula for 'CP'.

So my question is: Is there a way that everything is read as a string in DoCmd.TransferSpreadsheet() or any alternative?

I'm unclear on why you do not perform your 'data manipulation' before the import. — user4039065
@Jeeped Most of the other stuff needed is already on access form. I am trying to create another button in which user points to excel file and then it will export the formatted data in the access table. In my code initialtable is essentially a temp table. — USER420
And therein lies the rub! The classic difference between a spreadsheet and database. In a spreadsheet, you can input anything string/date/boolean/number in a column. Databases will punish you if you do not adhere to column's data type. Access' datetime fields requires each segment day, month, and year: MM/DD/YYYY or DD/MM/YYYY (depending on your locale). If time (HH:MM:SS) is left out, midnight is assumed 00:00:00. — Parfait

Slubee Slubee · Accepted Answer · 2016-01-25T19:43:52

You can manually import the table with all the columns as string and save the import and call from code as DoCmd.RunSavedImportExport "Your import name"

Importing Excel sheet into access

1 Answers