Using Automation in Access to address heading cells in Excel

Pat
5StarLounger
Posts: 1148
Joined: 08 Feb 2010, 21:27

Using Automation in Access to address heading cells in Excel

Post by Pat »

I have a generated Excel workbook from my bank which has a leading space in all headings.

I think I need to use automation in Access to address the heading cells in an excel sheet.
I have done this in the past but it has completely eluded me.
Will somebody help please.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using Automation in Access to address heading cells in E

Post by HansV »

Here is some sample code with comments. It uses late binding, so you don't have to set a reference to the Excel object library.
You can modify the code to suit your needs.

Code: Select all

Sub ProcessWorkbook()
    Dim xlApp As Object
    Dim xlWbk As Object
    Dim xlWsh As Object
    Dim lngCol As Long
    Dim lngLastCol As Long

    On Error GoTo ErrHandler
    ' Create a hidden instance of Excel
    Set xlApp = CreateObject("Excel.Application")
    ' Open a workbook
    Set xlWbk = xlApp.Workbooks.Open("C:\Downloads\BankStatement.xlsx")
    ' Refer to the first worksheet
    Set xlWsh = xlWbk.Worksheets(1)
    ' Last used column (-4159 = xlToLeft)
    lngLastCol = xlWsh.Cells(1, xlWsh.Columns.Count).End(-4159).Column
    ' Loop through columns
    For lngCol = 1 To lngLastCol
        ' Do something, e.g. remove leading and trailing spaces from cell
        With xlWsh.Cells(1, lngCol)
            .Value = Trim(.Value)
        End With
    Next lngCol
    ' Save and close workbook
    xlWbk.Close SaveChanges:=True

ExitHandler:
    On Error Resume Next
    ' Quit Excel
    xlApp.Quit

    ' Release memory
    Set xlWsh = Nothing
    Set xlWbk = Nothing
    Set xlWsh = Nothing
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub
Best wishes,
Hans

Pat
5StarLounger
Posts: 1148
Joined: 08 Feb 2010, 21:27

Re: Using Automation in Access to address heading cells in E

Post by Pat »

Thank you Hans, that is just what I was looking for.