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.
Using Automation in Access to address heading cells in Excel
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
-
- 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
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.
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
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Using Automation in Access to address heading cells in E
Thank you Hans, that is just what I was looking for.