Import File - Column Headers with Leading Spaces

[brsawvel]

I have an excel that exports from another program. Unfortunately, the source includes leading spaces in some of the column headers. I have the following command to import to a specific table with similar headers:

DoCmd.TransferSpreadsheet acimport, acspreadsheetTypeExcel12XML, "tblA", Me.fldA, True

[Me.fldA is a field with a browse button that drops the file name into that field.]

Since Access doesn't allow for a leading space in the table headers, is there an alternative? (I was thinking either a special character that represents a space or an edit of the xls before import)

 

[gemma-the-husky]

edit the excel file first. then import it.

 

[brsawvel]

Anybody else?

 

[JHB]

Import it to a temporary table, the use an update query to add the data to the final table.

 

[gemma-the-husky]

Import it to a temporary table, the use an update query to add the data to the final table.

true.

if fact, import to a temporary table, rename all the column names in the temporary table, verify that you have all the column names you expect, and then use the update query.

 

[sxschech]

This code trims spaces. Haven't looked at code for a while and copied pieces of what I thought was relevant, so if I missed something that causes an error, let me know.

Add this part if your code doesn't already have this for automating excel.

Public Sub ExcelToText(ByVal stfilepath As String)
Dim objapp As Object
    Dim wb As Object
    Dim lastCol As Long
 Set objapp = CreateObject("Excel.Application")
    objapp.Visible = True

If Dir(stfilepath) Then
        Set wb = objapp.Workbooks.Open(stfilepath, True, False)
End If
With wb.sheets(sheetIndex)
        .Activate

Trim Spaces code (may not need the if statement in your case)

If Forms!frmUtilities.cboImportFile = "QuickLook" Then 'trim the cells of spaces
            'http://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba
            'http://www.thespreadsheetguru.com/the-code-vault/a-fast-way-to-clean-trim-cell-values-with-vba-code
            lastrow = .Range("A1").currentregion.rows.Count
            lastCol = .Range("A1").currentregion.Columns.Count
            For Each Area In .Range(.Cells(1, 1), .Cells(lastrow, lastCol))
                Area.Value = .Evaluate("IF(ROW(" & Area.ADDRESS & "),CLEAN(TRIM(" & Area.ADDRESS & ")))")
            Next Area
        End If

Other code may need

End With
If globalintSheetIndex <> 0 Then
        wb.activesheet.Copy
        objapp.activeworkbook.SaveAs FilePath(stfilepath) & FileNameNoExt(stfilepath) & "txt.xlsx", FileFormat:=51
        wb.Close savechanges:=False
    Else
        wb.SaveAs FilePath(stfilepath) & FileNameNoExt(stfilepath) & "txt.xlsx", FileFormat:=51
        wb.Close 'savechanges:=False
    End If
    
    objapp.Quit

    Set objapp = Nothing