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






"CDM" <(E-Mail Removed)> wrote in message
news:27929768-168D-41CB-996E-(E-Mail Removed)...
> 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.