Ever try to import an Excel file into Access and find that some records were not imported because they didn’t match the field type (text, integer)? Access tries to predict the field type based on the data it sees and sometimes gets it wrong. This often occurs with Account Number fields which are sometimes numeric and sometimes text. Access thinks it has a numeric field, but then in record 500, it encounters an Account Number with an alpha character – and that record ends up in the Error file.
One solution is to first convert the Excel file to a csv and then perform the import. But here again, sometimes Access creates the same problem for itself (and you!).
Here’s a solution that will always work. The code below does the following:
Function: ConvertCellsToText – (this requires that you pass parameters that define the Excel application, workbook and worksheet).
1) Determines the used part of the worksheet
2) Converts every single cell in the used part of the worksheet to text by placing an apostrophe in front of each cell.
Typically, I run this using the following process:
- Convert the “txt” file using the ConvertCellsToTxt function.
- Copy the original Excel file, giving it a prefix of “txt”. Before this is done, I check to see if that file already exists (see Function: IsFile and ExtractFolderNameFromPath). If it does exist, it is deleted.
- Import the “txt” Excel file using the standard Transferspreadsheet function.
- Delete the “txt” Excel file.
The downside to this process is that it can take minutes to process very large spreadsheets. Realize that if you are importing a spreadsheet with 50,000 rows and 20 columns, the ConvertCellsToText function has to convert 1,000,000 cells.
The upside is: No more import errors!
Here is the code for the ConvertCellsToText function (I have commented out my error handling routine):
Public Function ConvertCellsToText(xlAPP As Excel.Application, xlWB As Excel.Workbook, xlWS As Excel.Worksheet)
‘—————————————————————–
‘Purpose: Converts every cell on the passed in spreadsheet to text by placing an apostrophe (‘) at
‘ the beginning of each cell. This ensures that there will be no errors when the excel
‘ sheet is imported into an Access table (Access may think that a column is numeric and
‘ when it encounters text, it creates an import error and the record is ignored.
‘ The file is not saved after the changes are made
‘CalledFrom:
‘Calls : basErrorHandler.ErrorHandler()
‘Assumes: Workbook/worksheet is passed to the subroutine
‘
‘Returns: True/False indicating success.
‘——————————————————————
On Error GoTo ErrorHandling:
Dim iLastRow As Long
Dim iLastColumn As Long
Dim vExcelSheet As Variant
Dim iCol As Integer
Dim iRow As Long
iLastRow = xlWB.ActiveSheet.UsedRange.Rows.Count
iLastColumn = xlWB.ActiveSheet.UsedRange.Columns.Count
vExcelSheet = xlWS.UsedRange.Value
For iRow = 1 To UBound(vExcelSheet, 1)
For iCol = 1 To UBound(vExcelSheet, 2)
If IsError(vExcelSheet(iRow, iCol)) Then
xlWS.Range(ColumnLetter(iCol) & iRow).Value = “‘”
Else
xlWS.Range(ColumnLetter(iCol) & iRow).Value = “‘” & vExcelSheet(iRow, iCol)
End If
Next iCol
Next iRow
ExitProc:
DoCmd.SetWarnings True
Exit Function
ErrorHandling:
‘Call ErrorHandler(“basUtils.ConvertCellsToText”, Err.Number, Err.Description)
GoTo ExitProc
End Function
