method ‘range of object’ global failed

have an access data base where i generate 2 excel reports as the final step. when i run report 1, the excel part works fine. when i try to run report 2, i get the message ‘method range of object’ global failed. if i close the database, reopen and run report 2, works fine. have tried multi things, none of which work.  at first thought, the excel app was still open from the first report and that was causing the issue.  put in code to make sure 1st excel app was closed. nothing worked.  i am calling a routine to insert a blank row when there is a change in a specific field. here is the code for the excel part….Public Sub Excelapp2()
On Error GoTo Err_excelapp2_click

‘OutFileName = “C:temp” + “claims for –  ” & strname & ” ” & Format(Now(), “mmDDYYYYHHMM”) + “.xls”

OutFileName = “C:temphosp_claims_report.xls”
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strname, OutFileName, True, “”
 
Set xlApp = CreateObject(“excel.application”)
Set xlbook = xlApp.ActiveWorkbook
Set xlsheet = xlApp.Workbooks.Open(OutFileName).Sheets(1)

xlsheet.Activate
xlsheet.Columns(“A:BA”).Select

Selection.Sort Key1:=Range(“A:A”), Order1:=xlAscending, _
               Key2:=Range(“B:B”), Order2:=xlAscending, _
               Header:=xlYes, OrderCustom:=1, Orientation:=xlTopToBottom, _
               DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortNormal, _
               DataOption3:=xlSortNormal

xlsheet.Range(“A2:BA2”).Select

xlApp.ActiveWindow.FreezePanes = True
xlsheet.Range(“a1:BA1”).WrapText = True
xlsheet.Range(“a1:BA1”).Font.Bold = True

‘xlsheet.Range(“U1:AA1”).Interior.ColorIndex = 6
‘xlsheet.Range(“BH1:BJ1”).Interior.ColorIndex = 44
xlsheet.Cells(1, 27).Value = “Procedure code”
xlsheet.Range(“A:A”).ColumnWidth = 11
xlsheet.Range(“B:B”).ColumnWidth = 14
xlsheet.Range(“C:C”).ColumnWidth = 44
xlsheet.Range(“D:D”).ColumnWidth = 13
xlsheet.Range(“E:E”).ColumnWidth = 7
xlsheet.Range(“F:F”).ColumnWidth = 14
xlsheet.Range(“G:H”).ColumnWidth = 16
xlsheet.Range(“I:I”).ColumnWidth = 19
xlsheet.Range(“J:J”).ColumnWidth = 12
xlsheet.Range(“K:K”).ColumnWidth = 14
xlsheet.Range(“M:M”).ColumnWidth = 12
xlsheet.Range(“N:N”).ColumnWidth = 14
xlsheet.Range(“N:N”).EntireColumn.Insert
xlsheet.Cells(1, 14).Value = “Benefit listed on benefit grid”
xlsheet.Range(“N1:V1”).Interior.ColorIndex = 4
xlsheet.Range(“O:O”).ColumnWidth = 17
xlsheet.Range(“U:W”).ColumnWidth = 13
xlsheet.Range(“X:Z”).ColumnWidth = 12
xlsheet.Range(“AA:AA”).ColumnWidth = 14
xlsheet.Range(“AB:AB”).ColumnWidth = 12
xlsheet.Range(“AF:AF”).ColumnWidth = 13
xlsheet.Range(“AG:AG”).ColumnWidth = 11
xlsheet.Range(“AH:AH”).ColumnWidth = 16
xlsheet.Range(“AI:AI”).ColumnWidth = 13
xlsheet.Range(“AJ:AK”).ColumnWidth = 12
xlsheet.Range(“AL:AL”).ColumnWidth = 13
xlsheet.Range(“AN:AP”).ColumnWidth = 12
xlsheet.Range(“AP:AS”).ColumnWidth = 15
xlsheet.Range(“BA:BA”).ColumnWidth = 12
xlsheet.Cells(1, 53).Value = “Pass/Fail”

Call InsertRowAtChangeInValue

MsgBox “file has been processed – hospital claims report available”
xlApp.ActiveWorkbook.Save
xlApp.ActiveWorkbook.Close “C:temp” + “hosp_claims_report.xls”
xlApp.Application.Quit

Set xlApp = Nothing
Set xlsheet = Nothing
Set xlbook = Nothing

Exit_excelapp2_Click:
 
    Exit Sub

Err_excelapp2_click:
    MsgBox Err.Description
    Resume Exit_excelapp2_Click
   
End Sub

 

 

here is the code for inserting the row…..

Private Sub InsertRowAtChangeInValue()
      ‘column B contains then scenario id, so want to insert a blank line when that changes
  For lRow = Cells(Cells.Rows.Count, “B”).End(xlUp).row To 2 Step -1
      If Cells(lRow, “B”) <> Cells(lRow – 1, “B”) Then
           Rows(lRow).EntireRow.Insert
      End If
  Next lRow
 
End Sub

 

 

thanks for your help

 

By: john newlander

Leave a Reply

Your email address will not be published. Required fields are marked *