Controlling Excel from Access VB

  • Thread starter Thread starter CDM
  • Start date Start date
C

CDM

My company software generates spreadsheet reports that I import into Access.
Before importing, I have to manually format the report by deleting the first
header row and changing the headings in the second row, etc. I would like to
automate this process by doing it with VB from within Access. However, when
selecting the first row I get an error message that says "Select method of
range class failed". What am I doing wrong? Here's my code:

Dim objWkBook as object
Dim objWkSheet as object
Set objWkBook = Getobject("Path\MyFile.xls")
Set objWkSheet = objWkbook.sheets(1)
objWkSheet.Range("A1:AZ1").Select

Thanks for any help you can offer.
 
I would tackle it like this:

Sub ChangeExcelHeadings()

Const strcWkBkPathName As String = _
"C:\CDM.xls"

Dim objXL As Excel.Application
Dim objWkBook As Excel.Workbook
Dim objWkSheet As Excel.Worksheet
Dim objRNG As Excel.Range

Dim I As Integer
Dim astrHeadings() As String

On Error GoTo Error_GetExcelData

' Open Excel:
On Error Resume Next
Set objXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Clear
Set objXL = CreateObject("Excel.Application")
End If
objXL.Visible = True

' Open Workbook:
Set objWkBook = objXL.Workbooks.Open(strcWkBkPathName)
Set objWkSheet = objWkBook.Worksheets(1)

' Delete cells A1:AZ1:
Set objRNG = objWkSheet.Range("A1:AZ1")
objRNG.Delete xlShiftUp

' Reset range:
Set objRNG = objWkSheet.Range("A1:AZ1")

' Store sample new row headings in array
' for demo purposes:
ReDim astrHeadings(1 To objRNG.Cells.Count)
For I = LBound(astrHeadings) To UBound(astrHeadings)
astrHeadings(I) = "NewHeading" & CStr(I)
Next

' Write new row headings:
For I = 1 To objRNG.Cells.Count
objRNG.Cells(1, I) = astrHeadings(I)
Next

' Adjust column widths:
objRNG.Columns.AutoFit

' Clean up:
Set objRNG = Nothing
Set objWkSheet = Nothing
If Not objWkBook Is Nothing Then
objWkBook.Close SaveChanges:=True
Set objWkBook = Nothing
End If
objXL.Quit

Exit_GetExcelData:

Exit Sub

Error_GetExcelData:

MsgBox "Error No: " & Err.Number _
& vbNewLine _
& Err.Description, _
vbExclamation + vbOKOnly, _
"Error Information"
Resume Exit_GetExcelData:

End Sub


Geoff
 
Back
Top