Intermittent Run time error 91

[alxndrxvier]

This is the code I am using to create a couple of spreadsheets and paste some data into them from Access. If I open Access and run this code it works just fine. It copies the files, pastes the data, modifies as required, etc. However, if I run the code again without closing access I get "Run-time error '91': Object variable or With block variable not set", when the code gets to the Do function. Clicking stop, and immediately running the script again causes it to work perfectly. This seems to happen every other time I run the code. I have no idea what is going on. Any help is greatly appreciated.

Private Sub IAprep()


Dim fromname As String
Dim toname As String
Dim qryname As String
Dim objXLApp As Object
Dim objXLWb As Object
Dim objXLWs As Object
Dim expfile1 As String
Dim expfile2 As String
Dim strsql As String
Dim strsql1 As String
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim currval As String


'move data file to linked location


    fromname = "C:\Users\jslay\Desktop\HDL to Outlet movement process folder\Consolidated " & Format(Date, "YY-MM-DD") & ".xlsx"
    toname = "C:\Users\jslay\Desktop\HDL to Outlet movement process folder\Linked files\Consolidatedprep.xlsx"
    
    FileCopy fromname, toname
    
    KillProperly (fromname)
    
'create data tables
    
    DoCmd.SetWarnings False
        
    qryname = "qry_10_mktbl_processdata"
    DoCmd.OpenQuery qryname, acNormal, acEdit
    
    qryname = "qry_11_mktbl_totalsdata"
    DoCmd.OpenQuery qryname, acNormal, acEdit


    KillProperly (toname)


    DoCmd.SetWarnings True


'create working files from templates


    fromname = "C:\Users\jslay\Desktop\HDL to Outlet movement process folder\Templates\Consolidated Template.xlsx"
    expfile1 = "C:\Users\jslay\Desktop\HDL to Outlet movement process folder\HDL to Outlet process data - Final - " & Format(Date, "YY-MM-DD") & ".xlsx"
    FileCopy fromname, expfile1
    fromname = "C:\Users\jslay\Desktop\HDL to Outlet movement process folder\Templates\Consolidated Totals Template.xlsx"
    expfile2 = "C:\Users\jslay\Desktop\HDL to Outlet movement process folder\HDL to Outlet Totals - " & Format(Date, "YY-MM-DD") & ".xlsx"
    FileCopy fromname, expfile2
    
'set and create data sets
    
    strsql = "SELECT tbl_01_hdltooutletprocessdata.* FROM tbl_01_hdltooutletprocessdata;"
    strsql1 = "SELECT tbl_02_totals.* FROM tbl_02_totals;"
    
    Set rst = CurrentDb.OpenRecordset(strsql, dbOpenSnapshot)
    Set rst1 = CurrentDb.OpenRecordset(strsql1, dbOpenSnapshot)
    
'open excel for first file and copy data
    
    Set objXLApp = CreateObject("Excel.Application")
    objXLApp.Application.Visible = True
    Set objXLWb = objXLApp.Workbooks.Open(expfile1)
    Set objXLWs = objXLWb.Worksheets("Sheet1")
    objXLWs.Range("A2").CopyFromRecordset rst


'apply data format changes


    With objXLApp.ActiveSheet
    .Range("H2").Select
    
    Do While ActiveCell.Value <> ""
    currval = ActiveCell.Value
    If IsNumeric(currval) Then
    currval = CLng(currval)
    .Application.ActiveCell.Value = currval
    End If
    ActiveCell.Offset(1, 0).Select
    Loop
    
    .Range("A2").Select
    
    .Range("A:J").AutoFilter Field:=8, Criteria1:="Invalid STS Item Number"
    
    End With


    objXLWb.Save
    objXLWb.Close


'open second file and copy data


    Set objXLWb = objXLApp.Workbooks.Open(expfile2)
    Set objXLWs = objXLWb.Worksheets("Sheet1")
    objXLWs.Range("A3").CopyFromRecordset rst1
    
    objXLWb.Save
    objXLWb.Close
    objXLApp.Quit


Exit_iaprep:


Set objXLApp = Nothing
Set objXLWb = Nothing
Set objXLWs = Nothing


    rst.Close
    If Not rst Is Nothing Then
        Set rst = Nothing
    End If
    rst1.Close
    If Not rst1 Is Nothing Then
        Set rst1 = Nothing
    End If
    Exit Sub




End Sub

[Micron]

Do While ActiveCell.Value <> ""

You're saying it errors here, but not the first time? And this is a run time error only? It would seem that the object "ActiveCell" (meaning the reference) is lost the second time through. Not sure since there's no indication of any global or form variables coming in to play the first time but not the second. If it's not that, I would suggest not interspersing parts of your With block within If blocks:

Do While ActiveCell.Value <> ""
currval = ActiveCell.Value
If IsNumeric(currval) Then
currval = CLng(currval)
.Application.ActiveCell.Value = currval
End If
ActiveCell.Offset(1, 0).Select
Loop
See how you have .Application, which requires it to be within a With block, but you have it inside an IF block. No idea why it would run once but not again if that turns out to be the issue.

Some other things I wonder about in your code, but surely you want to solve the error first...

[alxndrxvier]

You're saying it errors here, but not the first time? And this is a run time error only?

Correct, and correct. My apologies that I wasn't clearer, I keep forgetting that I've been staring at this code for a couple of days and thus might be able to find what I'm referencing better.

Not sure since there's no indication of any global or form variables coming in to play the first time but not the second.

There are no global variables in this sub or the other ones within this db. In this case I tried to make this a stand alone sub in order to allow me to run a different one several times if necessary then run this one at the end of the day.

If it's not that, I would suggest not interspersing parts of your With block within If blocks:

The loop I have is contained within a "with" block, however the .application you mention is contained with an If because I do not need to use it on every single cell, just the ones that are numeric. Do you suggest that I use a with block just for that line? Rather than having a single with block that covers the several functions being performed on the current spreadsheet.

Some other things I wonder about in your code, but surely you want to solve the error first...

Such as? I'm mostly self taught so I know I have more to learn, if you have questions and they might help me figure this out or get better in the future I am happy to answer them.

[John_G]

I have found (from frustrating experience) that when working with Excel from Access VBA that all references to Excel objects must be fully referenced, meaning that instead of:

currval = ActiveCell.Value
or
ActiveCell.Offset(1, 0).Select

you need

currval = objXLApp.ActiveSheet.ActiveCell.Value
or
objXLApp.ActiveSheet.ActiveCell.Offset(1, 0).Select

otherwise you get weird errors like that (and they're a "pain" to find!).

[alxndrxvier]

That did it! Took awhile to set all of the excel interface references to absolute, but now that I have it works exactly as I wanted. Thank you so much.

[Micron]

So what does your code look like now?

[alxndrxvier]

Basically the same, but with some absolute references.

Private Sub IAprep()


Dim fromname As String
Dim toname As String
Dim qryname As String
Dim objXLApp As Object
Dim objXLWb As Object
Dim objXLWs As Object
Dim expfile1 As String
Dim expfile2 As String
Dim strsql As String
Dim strsql1 As String
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim currval As String


'move data file to linked location


    fromname = "C:\Users\jslay\Desktop\HDL to Outlet movement process folder\Consolidated " & Format(Date, "YY-MM-DD") & ".xlsx"
    toname = "C:\Users\jslay\Desktop\HDL to Outlet movement process folder\Linked files\Consolidatedprep.xlsx"
    
    FileCopy fromname, toname
    
    KillProperly (fromname)
    
'create data tables
    
    DoCmd.SetWarnings False
        
    qryname = "qry_10_mktbl_processdata"
    DoCmd.OpenQuery qryname, acNormal, acEdit
    
    qryname = "qry_11_mktbl_totalsdata"
    DoCmd.OpenQuery qryname, acNormal, acEdit


    KillProperly (toname)


    DoCmd.SetWarnings True


'create working files from templates


    fromname = "C:\Users\jslay\Desktop\HDL to Outlet movement process folder\Templates\Consolidated Template.xlsx"
    expfile1 = "C:\Users\jslay\Desktop\HDL to Outlet movement process folder\HDL to Outlet process data - Final - " & Format(Date, "YY-MM-DD") & ".xlsx"
    FileCopy fromname, expfile1
    fromname = "C:\Users\jslay\Desktop\HDL to Outlet movement process folder\Templates\Consolidated Totals Template.xlsx"
    expfile2 = "C:\Users\jslay\Desktop\HDL to Outlet movement process folder\HDL to Outlet Totals - " & Format(Date, "YY-MM-DD") & ".xlsx"
    FileCopy fromname, expfile2
    
'set and create data sets
    
    strsql = "SELECT tbl_01_hdltooutletprocessdata.* FROM tbl_01_hdltooutletprocessdata;"
    strsql1 = "SELECT tbl_02_totals.* FROM tbl_02_totals;"
    
    Set rst = CurrentDb.OpenRecordset(strsql, dbOpenSnapshot)
    Set rst1 = CurrentDb.OpenRecordset(strsql1, dbOpenSnapshot)
    
'open excel for first file and copy data
    
    Set objXLApp = CreateObject("Excel.Application")
    objXLApp.Application.Visible = True
    Set objXLWb = objXLApp.Workbooks.Open(expfile1)
    Set objXLWs = objXLWb.Worksheets("Sheet1")
    objXLWs.Range("A2").CopyFromRecordset rst


'apply data format changes


    With objXLApp.ActiveSheet
    objXLApp.ActiveSheet.Range("H2").Select
    
    Do While objXLApp.Application.ActiveCell.Value <> ""
    currval = objXLApp.Application.ActiveCell.Value
    If IsNumeric(currval) Then
    currval = CLng(currval)
    objXLApp.Application.ActiveCell.Value = currval
    End If
    objXLApp.Application.ActiveCell.Offset(1, 0).Select
    Loop
    
    objXLApp.Application.Range("A2").Select
    
    objXLApp.Application.Range("A:J").AutoFilter Field:=8, Criteria1:="Invalid STS Item Number"
    
    End With


    objXLWb.Save
    objXLWb.Close


'open second file and copy data


    Set objXLWb = objXLApp.Workbooks.Open(expfile2)
    Set objXLWs = objXLWb.Worksheets("Sheet1")
    objXLWs.Range("A3").CopyFromRecordset rst1
    
    objXLWb.Save
    objXLWb.Close
    objXLApp.Quit


Exit_iaprep:


Set objXLApp = Nothing
Set objXLWb = Nothing
Set objXLWs = Nothing


    rst.Close
    If Not rst Is Nothing Then
        Set rst = Nothing
    End If
    rst1.Close
    If Not rst1 Is Nothing Then
        Set rst1 = Nothing
    End If
    Exit Sub




End Sub

[Micron]

The loop I have is contained within a "with" block, however the .application you mention is contained with an If because I do not need to use it on every single cell, just the ones that are numeric. Do you suggest that I use a with block just for that line?

I can see that. But it's not good practice. If you're going to nest things, maybe like this

With Me.txtSomething
  .backcolor = vbRed
  .backstyle = transparent
  .etc
  If Me.txtNothing = "test" Then
    do some stuff
  End If
  .etc
End With

Not

With Me.txtSomething
  .backcolor = vbRed
  .backstyle = transparent
  If Me.txtNothing = "test" Then
    do some stuff
    .etc (part of the With block - it's orphaned because it's inside an If)
   End If
End With

I try very hard not to nest things like If within With, but would nest a With inside an If or Select Case - hope you see the difference. It's quite possible that your problem went away not because of the explicit reference, but because the new code eliminated the issue of an incomplete block. For example, if you do a For loop and incorrectly nest an If or With, you can get a Next Without For error even though the Next is there somewhere. I have used Automation like this with Excel and didn't have to use the full reference when I've already declared a variable for it. Example

Set xls = xlw.Worksheets(shtName)
With xls
'delete data in the ranges in case fewer records are being entered
  .Range(rnge1).ClearContents
  .Range(rnge2).ClearContents
End With
Set xlc = xls.Range(cellStart) ' this is the top left cell into which data goes
rst.MoveFirst

Notice when xlc is set, it does not use the full reference for the range - it uses the xls object variable. Think about it - why would you have to declare an object then not be able to use it somewhere? Because there's a problem with the syntax, not the object.

With your new code, you took care of some of the things I would have suggested. For what it's worth, here's my take. Not that there's anything wrong with what you have now - just showing another approach. Some notes in there & if I had used the CurrentDb.Execute method, it would be different again.

Private Sub IAprep()
Dim fromname As String, toname As String '[I wouldn't bother with this as the names are not too long, qryname As String]
Dim objXLApp As Object, objXLWb As Object, objXLWs As Object
Dim expfile1 As String, expfile2 As String, strsql As String
Dim strsql1 As String, currval As String
Dim rst As DAO.Recordset, rst1 As DAO.Recordset

'move data file to linked location
fromname = "C:\Users\jslay\Desktop\HDL to Outlet movement process folder\Consolidated " & Format(Date, "YY-MM-DD") & ".xlsx"
toname = "C:\Users\jslay\Desktop\HDL to Outlet movement process folder\Linked files\Consolidatedprep.xlsx"
            
FileCopy fromname, toname        
KillProperly (fromname)
            
'create data tables
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_10_mktbl_processdata", acNormal, acEdit 'if this is an action query, I'd use the .Execute method of the CurrentDb Object, then warnings don't have to be cycled on/off, can't remain off if an error causes abnormal execution, plus the method generates an error which can be customized.
            
DoCmd.OpenQuery "qry_11_mktbl_totalsdata", acNormal, acEdit
KillProperly (toname)
DoCmd.SetWarnings True

'create working files from templates
fromname = "C:\Users\jslay\Desktop\HDL to Outlet movement process folder\Templates\Consolidated Template.xlsx"
expfile1 = "C:\Users\jslay\Desktop\HDL to Outlet movement process folder\HDL to Outlet process data - Final - " & Format(Date, "YY-MM-DD") & ".xlsx"

FileCopy fromname, expfile1
fromname = "C:\Users\jslay\Desktop\HDL to Outlet movement process folder\Templates\Consolidated Totals Template.xlsx"
expfile2 = "C:\Users\jslay\Desktop\HDL to Outlet movement process folder\HDL to Outlet Totals - " & Format(Date, "YY-MM-DD") & ".xlsx"
FileCopy fromname, expfile2
            
'set and create data sets
strsql = "SELECT tbl_01_hdltooutletprocessdata.* FROM tbl_01_hdltooutletprocessdata;"
strsql1 = "SELECT tbl_02_totals.* FROM tbl_02_totals;"  
Set rst = CurrentDb.OpenRecordset(strsql, dbOpenSnapshot)
Set rst1 = CurrentDb.OpenRecordset(strsql1, dbOpenSnapshot)
   
'open excel for first file and copy data
Set objXLApp = CreateObject("Excel.Application")
objXLApp.Application.Visible = True
Set objXLWb = objXLApp.Workbooks.Open(expfile1)
Set objXLWs = objXLWb.Worksheets("Sheet1")
objXLWs.Range("A2").CopyFromRecordset rst

'apply data format changes
objXLApp.ActiveSheet.Range("H2").Select

Do While objXLApp.Application.ActiveCell.Value <> ""
  currval = objXLApp.Application.ActiveCell.Value
  If IsNumeric(currval) Then
      currval = CLng(currval)
      objXLApp.Application.ActiveCell.Value = currval
  End If
  objXLApp.Application.ActiveCell.Offset(1, 0).Select
Loop
            
objXLApp.Application.Range("A2").Select
objXLApp.Application.Range("A:J").AutoFilter Field:=8, Criteria1:="Invalid STS Item Number"

objXLWb.Save
objXLWb.Close

'open second file and copy data
Set objXLWb = objXLApp.Workbooks.Open(expfile2)
Set objXLWs = objXLWb.Worksheets("Sheet1")
objXLWs.Range("A3").CopyFromRecordset rst1
objXLWb.Save
objXLWb.Close
objXLApp.Quit

Exit_iaprep:
Set objXLApp = Nothing
Set objXLWb = Nothing
Set objXLWs = Nothing

rst.Close
rst1.Close
Set rst = Nothing 'don't need to check if it's Nothing & no reason it would be - won't cause an error anyway
Set rst1 = Nothing

Exit Sub 'why this and the line label Exit_iaprep (unless you have an error handler in this sub that you didn't post)?

End Sub

[alxndrxvier]

I think I see what you are getting at. Some good advice, I will definitely consider it the next time I need to write some code. Thank you.

[John_G]

Orange & Micron -

Now I recall the reason for my fully-qualified comment. I had an application in which I used MS Access to interact (in that case) MS Word. It was working perfectly until they upgraded (?) to A2013, when it started failing consistently not just with an error message but a complete crash of Word. After a lot of very frustrating attempts to get it going again, someone gave me the tip to use the fully qualified references, and that was the solution.

Micron, you asked "...why would you have to declare an object then not be able to use it somewhere?". Very good point, and that's what I was using, but evidently A2013 or Word 2013 didn't see things that way. IMO, it's woth keeping in mind if we have users here experiencing the same issues in A2013.

Cheers!

John