1004: application defined or object defined error (2nd Loop)

Question

I'm writing some vb to export data from access to excel. Once passed to excel, I use code to format one spreadsheet in the workbook, then save and close the file. It is supposed to loop through this code several times. When I execute the code, it creates the first itteration of the spreadsheet, but on the second pass it returns a 1004: application defined or object defined error. Been searching for a solution and cannot find. Help please?

===============================================

Code is below

On Error GoTo errorhandler

Dim strtable As String
Dim strworksheetpath As String
Dim pickersct As Integer
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSh As Excel.Worksheet

pickersct = 1
Me.Text7 = 1

Do Until pickersct > Me.Text1

    Me.Text7.Requery
    strworksheetpath = "D:\My Documents\Deliverable" & pickersct & ".xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qry_pickers_Picks_Export", strworksheetpath

    Set xlApp = New Excel.Application
    Set xlWB = xlApp.Workbooks.Open("D:\My Documents\deliverable" & pickersct & ".xls")
    Set xlSh = xlWB.Sheets("qry_pickers_Picks_Export")

        Sheets("qry_pickers_Picks_Export").Activate 'Error occurs after this line
        Sheets("qry_pickers_Picks_Export").Range("H2").Select
        ActiveCell.FormulaR1C1 = "=IF(RC[-3]=1,-1,0)"
        Sheets("qry_pickers_Picks_Export").Range("I2").Select
        ActiveCell.FormulaR1C1 = "=IF(RC[-2]=1,-1,0)"
        Sheets("qry_pickers_Picks_Export").Range("J2").Select
        ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
        Sheets("qry_pickers_Picks_Export").Range("J2").Select
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
            Formula1:="=1"

    xlWB.Save
    xlWB.Close
    xlApp.Quit
    Set xlApp = Nothing

    pickersct = pickersct + 1
    Me.Text7 = Me.Text7 + 1
Loop

errorHandlerExit:
  Exit Sub

errorhandler:
  MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
  Resume errorHandlerExit

its advised not to use .select but use other methods. you can use the following code to do 2 steps in 1: xlSh.Range("H2").FormulaR1C1 = "=IF(RC[-3]=1,-1,0)" plus you define xlSh but then don't use it, as in the code in my comment I included this. — DragonSamu

Andre Andre · Accepted Answer · 2015-08-18T13:52:10

Recorded VBA from Excel usually doesn't work directly in Access. I'm surprised it runs for the first iteration.

Sheets or ActiveCell don't work without reference to the Excel application object. It is best to assign objects and always work with them.

Replace Sheets("qry_pickers_Picks_Export") with xlSh.
Don't use .Select and ActiveCell.
Specify the range, and set its properties, e.g.
xlSh.Range("H2").FormulaR1C1 = "=IF(RC[-3]=1,-1,0)"

1004: application defined or object defined error (2nd Loop)

Code is below

1 Answers