Changing field names in an excel sheet from Access

[Pat]

I have an Excel file that has 12 sheets and each of these sheets has field names which are not left justified. When trying to import these sheets into Access i get an error saying "The search key was not found in any records".

I need some code to trim these field names prior to importing them into Access using TransferSpreadsheet.

[Pat]

I have found some code to do this however when it reaches the pointed line it errors with:
File xxx.xls is already open. Opening it again will make you lose your changes. Do you wish to open xxx.xls again?

Code: Select all

    Set xlApp = CreateObject("Excel.Application")
    Set colWorksheets = New Collection
    Set objWorkbook = xlApp.workbooks.Open(sExcelName, , False, , _
          strpassword)
    For lngCount = 1 To objWorkbook.Worksheets.Count
          colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
    Next lngCount
    For lngCount = 1 To objWorkbook.Worksheets.Count
        Set mySheet = xlApp.workbooks.Open(sExcelName).sheets(lngCount)      <------------------------------------
        Dim iCol As Integer
        For iCol = 1 To 5
            If iCol = 5 Then
                '   toyota
                mySheet.cells(1, iCol) = "UOM"
            End If
            mySheet.cells(1, iCol) = Trim(mySheet.cells(1, iCol))
        Next iCol
    Next lngCount

What line of code do i need here to address the sheet?

[HansV]

The line

Set mySheet = xlApp.workbooks.Open(sExcelName).sheets(lngCount)

tries to open the workbook again. There is no need for this - you have already opened it and set the variable objWorkbook to refer to it. Change the line to

Set mySheet = objWorkbook.Sheets(lngCount)

[Pat]

Thank you Hans, i think i may have asked you that before.