modifying an Excel spreadsheet with V

[jaarso]

test

 

[FDibbins]

is there (was there) a question here?

 

[jaarso]

Yes. I was in the process naming the thread and something happened with my keyboard. Anyhow, the intended title is "modifying an Excel spreadsheet with VBA code in an Access Form." My code works most of the time but the times that it does not work I am getting the error:

Run-time error '1004':
Method 'Rows' of object '_Global' failed
Any ideas on why the code only works sometimes?

Option Compare Database
Public Sub removerowsPGRpt()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSh As Excel.Worksheet
Dim lastRow As Long
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open("D:\PG_Rpt_CustomerExportPT.xls") '<modify as="" needed
Set xlSh = xlWB.Sheets("PG_Rpt_CustomerExportPT.rpt")         '<modify as="" needed


'Delete first 5 rows
Rows("1:5").Select
Selection.Delete Shift:=xlUp

'Delete rows after last record
With ActiveSheet
lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
Rows(lastRow + 1 & ":" & Rows.Count).Delete
End With
xlWB.Save
xlWB.Close
xlApp.Quit
Set xlApp = Nothing
End Sub[CODE]



The debugger is highlighting this "Rows("1:5").Select."


</modify></modify>

 

[Norie]

When you are automating Excel from Access, or any other application, you should make sure everything is fully qualified.

In your code you have Rows("1:5") without any worksheet/workbook/anything reference.

Also, you shouldn't use ActiveSheet use the reference to the worksheet you created earlier.

Try this.

Public Sub removerowsPGRpt()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSh As Excel.Worksheet
Dim lastRow As Long

    Set xlApp = New Excel.Application
    Set xlWB = xlApp.Workbooks.Open("D:\PG_Rpt_CustomerExportPT.xls")
    Set xlSh = xlWB.Sheets("PG_Rpt_CustomerExportPT.rpt")
    
    ' Delete first 5 rows
    With xlSh
        .Rows("1:5").Delete Shift:=xlUp
        lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        .Rows(lastRow + 1 & ":" & .Rows.Count).Delete
    End With
    
    xlWB.Save
    xlWB.Close
    xlApp.Quit
    Set xlApp = Nothing
    
End Sub

 

[jaarso]

I used your code and it works. Thank you very much Norie!!

When you are automating Excel from Access, or any other application, you should make sure everything is fully qualified.

In your code you have Rows("1:5") without any worksheet/workbook/anything reference.

Also, you shouldn't use ActiveSheet use the reference to the worksheet you created earlier.

Try this.

Public Sub removerowsPGRpt()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSh As Excel.Worksheet
Dim lastRow As Long

    Set xlApp = New Excel.Application
    Set xlWB = xlApp.Workbooks.Open("D:\PG_Rpt_CustomerExportPT.xls")
    Set xlSh = xlWB.Sheets("PG_Rpt_CustomerExportPT.rpt")
    
    ' Delete first 5 rows
    With xlSh
        .Rows("1:5").Delete Shift:=xlUp
        lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        .Rows(lastRow + 1 & ":" & .Rows.Count).Delete
    End With
    
    xlWB.Save
    xlWB.Close
    xlApp.Quit
    Set xlApp = Nothing
    
End Sub