Error 91 driving me nuts (1 Viewer)

[TonyFried]

To explain. I'm trying to open an Excel workbook, update its data from the database, save it to a folder and with a filename chosen by the user. All good so far. Code runs fine on first iteration but then fails with runtime error 91 on second iteration (and presumably the following ones). Testing shows that, on the second iteration, while the workbook opens and is visible, the is no active workbook (?) thus causing the error. The fail happens at "activeworkbook.refreshall". Any help much appreciated, (oh, I have tried commenting out the set XlApp=nothing line),code below:

Private Sub update_all_Click()
Dim db              As DAO.Database
Dim qdf             As DAO.QueryDef
Dim prm             As DAO.Parameter
Dim lngRowsAffected As Long
Dim lngRowsDeleted As Long
Dim fileName As Variant
Set db = CurrentDb
fileName = Dir("\\Svrfiles\access databases\Portfolio Test\")
While fileName <> ""
 DoCmd.OpenQuery "QryDeleteCash", acViewNormal
 On Error GoTo 0
    DoCmd.TransferSpreadsheet acLink, , "TblInputSht", "\\Svrfiles\access databases\Portfolio Test\" & fileName, False, "Input Sheet!D6:E32"
    DoCmd.TransferSpreadsheet acLink, , "TblInputShtName", "\\Svrfiles\access databases\Portfolio Test\" & fileName, False, "Input Sheet!E3:E3"
    Set qdf = db.QueryDefs("QryImport2")
    db.Execute "QryImport2", dbFailOnError
        For Each prm In qdf.Parameters
            prm = Eval(prm.Name)
        Next prm
 
    qdf.Execute
    lngRowsAffected = db.RecordsAffected
    DoCmd.DeleteObject acTable, "TblInputShtName"
    DoCmd.DeleteObject acTable, "TblInputSht"
    
    Dim xlApp As Excel.Application
    Set xlApp = CreateObject("Excel.Application")
    Set wbOutput = xlApp.Workbooks
        xlApp.Visible = True
        xlApp.Workbooks.Open "\\Svrfiles\ACCESS DATABASES\cashrptgraph.xlsm", True, False
        ActiveWorkbook.RefreshAll
            TWait = Time
            TWait = DateAdd("s", 10, TWait)
                Do Until TNow >= TWait
                    TNow = Time
                Loop
    fsName = Excel.Application.GetSaveAsFilename
    ActiveWorkbook.SaveAs fileName:=fsName
    ActiveWorkbook.Close
    xlApp.Quit
    Set xlApp = Nothing
    fileName = Dir
        If fileName <> "" Then
            DoCmd.OpenQuery "QryDeleteCash", acViewNormal
        End If
Wend
End Sub

 

[Minty]

I'd move the DIm xlApp statement out of the loop for starters? That makes no sense.

 

[Gasman]

Perhaps comment out
On Error GoTo 0
or use an error routine

 

[TonyFried]

Thanks Minty - the consequence of loading in code snippets! Unfortunately, made no difference. I'm sure it's all linked to the fact that the second iteration opens the Excel file but for some reason it never becomes "active" (tested that with a message box to display the active worksheets count and name)

    MsgBox "Number of open workbooks is " & Workbooks.Count
    'MsgBox "The name of the active workbook is " & ActiveWorkbook.Name

the count on iteration is 0 and the name is (clearly) blank.

 

[TonyFried]

Gasman
If I had no OnError Goto statement, the code would just stop....
To trap errors, I need to find out what to trap (i.e. what I'm trying to discover here)
thanks for the help tho...

 

[Minty]

I'm with Gasman - comment out the on error bit whilst you are debugging.
Then you at least know where it stops. (Which I know you know, but it lets you integrate things as they stand in the immediate window.)

Anyway - try specifically closing your
xlApp.Workbooks.Open "\\Svrfiles\ACCESS DATABASES\cashrptgraph.xlsm", True, False

I suspect it's still open in the background after the first iteration, as you save it as another file name.

 

[TonyFried]

But wouldn't

xlApp.Quit

do that?
Have checked with Task Manager that there are no Excel processes still running....
Is there a way of specifically testing for (and closing) cashrptgraph.xlsm (or any other workbook for that matter)? -my lack of code skills showing here....

 

[TonyFried]

Update
Rechecked TaskManager and there were several Excel processes hidden there "waiting for a network process to complete" but not linked to any specific workbook. Will delve further.....

 

[TonyFried]

I'm still trying...
Minty - trying to close the original file using

wbOutput("\\Svrfiles\access databases\Portfolio Test\").Close Savechanges:=False

simply creates a "subscript out of range" error (because that file is not there - set a breakpoint so I could check).
Further - if I close the background Excel processes before the next iteration proceeds, the code fails at the same point with error 462 "remote server machine does not exist or is unavailable".
Oh dear.....

 

[Minty]

I've seen orphaned Excel instances left behind before, it was one of the reasons I suggested it.

Just to be clear with your descriptions you are creating files here
Svrfiles\access databases\Portfolio Test
Then opening a different file here
\\Svrfiles\ACCESS DATABASES\cashrptgraph.xlsm
And saving it wherever the user specifies.

Also, I can't see a declaration of wbOutput anywhere.

Make sure you have Option Explicit at the top of your code EVERYWHERE !

 

[Micron]

Your code needs further study for a complete review, but for now I will point out this:
You're using an old form While Wend, which I would not for a few reasons. Regardless, While fileName is risky because if it ever becomes null or "" then the condition no longer exists. But that can't happen, you say? I would argue that because you are using a parameter (or is it keyword) for a variable, then later, as a parameter, then again as a variable. What can happen in such a case? I don't know and wouldn't be in that position if I used a naming convention consistently. You only use it some of the time. Where is this happening? Here...
ActiveWorkbook.SaveAs fileName:=fsName

 

[Micron]

If I had no OnError Goto statement, the code would just stop....

You disable it anyway: On Error GoTo 0
Maybe that's why you had several files running in the background without knowing.
EDIT - something missing here too (like maybe the Add method)? Or it's just unnecessary?
Set wbOutput = xlApp.Workbooks
and you don't actually activate a work book >>ActiveWorkbook.RefreshAll but I'm not sure if simply opening it makes it active.
If you want to automate a workbook (e.g. ActiveWorkbook.RefreshAll) I think you ought to create the object, but you don't
TWait and TTime are not declared either; not that it's to blame for your issue. Just bad form.
Add fsName to that list

 

[arnelgp]

you remove this:

Set wbOutput = xlApp.Workbooks

instead:

Set wbOutput = xlApp.Workbooks.Open("\\Svrfiles\ACCESS DATABASES\cashrptgraph.xlsm", True, False)

replace any instance of Activeworkbook with wbOutput.

 

[Isaac]

OP's On Error goto 0 is appropriate for debugging. That's the level that will debug, rather than ignoring the error or going to a label.

I'm still trying...
Minty - trying to close the original file using

wbOutput("\\Svrfiles\access databases\Portfolio Test\").Close Savechanges:=False

simply creates a "subscript out of range" error (because that file is not there - set a breakpoint so I could check).
Further - if I close the background Excel processes before the next iteration proceeds, the code fails at the same point with error 462 "remote server machine does not exist or is unavailable".
Oh dear.....

Try wbOutput.Close False
(for that line anyway - I"m having trouble keeping up with all the various issues represented in this single thread)

 

[Micron]

OP's On Error goto 0 is appropriate for debugging.

So? You're saying the same thing as me. OP seems to think that that line is needed to debug. It would be the same thing if it wasn't there at all so there's no 'ignoring' an error if there's no error handler, and in that code there isn't one. I'd say it's not unusual to have that line to turn it off temporarily but in conjunction with some form of error handling. To have that line and that alone seems pointless to me.

 

[Isaac]

Yes it is pointless because it's the default. I apologize if my post was unclear, it's just that I thought I saw a number of posts near the beginning of the thread recommending that he comment out that line which made no sense to me because that is the way that it should be.
And yes, quite possibly I'm saying the same as you.

 

[The_Doc_Man]

Rechecked TaskManager and there were several Excel processes hidden there "waiting for a network process to complete" but not linked to any specific workbook. Will delve further.....

Caused by the following two lines occurring inside the loop.

    Dim xlApp As Excel.Application
    Set xlApp = CreateObject("Excel.Application")

Declare and create ONE app object outside the loop before it starts, then just open and close workbooks from it inside the loop, then close the app object outside the loop after the loop-end condition has been satisfied.

Got no idea offhand why you get the Error 91 (No object) other than, as you noted, the workbook in question wasn't opened properly. But that simplification I suggested would help you isolate on the code of the workbooks better, which surely would help you find the reason for the failure with fewer object actions. The idea that you had a backlog of Excel images floating around suggests that some other error is occurring and you are not seeing it.

 

[Gasman]

Yes it is pointless because it's the default. I apologize if my post was unclear, it's just that I thought I saw a number of posts near the beginning of the thread recommending that he comment out that line which made no sense to me because that is the way that it should be.
And yes, quite possibly I'm saying the same as you.

That was me?
Not something I've ever used (well the 0 verstion) and when I looked here, it states

On Error GoTo 0

Disables any enabled error handler in the current procedure.

which I read as disabling error handling?

 

[Micron]

No that was to me. I find that information (if it's the same I've read before) misleading. It doesn't turn off error handling per se (I mean by Access). It 'disables' error handling that you've initiated. In this case, none was initiated and my point was "might as well remove the line entirely - it would be the same as having no error handling at all in this code"
To prove these points, write a little code snippet like

Dim intTest As Integer
On Error GoTo 0
intTest = 1/0

You will raise an error, and you won't be able to escape it.
EDIT - When you think of it another way, if there is no error handling routine, then any errors are unhandled. Those will still arise. GoTo 0 turns disables error handlers, not the 'handling' by Access of errors.

 

[cheekybuddha]

Hi,

Your error is here:

    fsName = Excel.Application.GetSaveAsFilename
    ActiveWorkbook.SaveAs fileName:=fsName
    ActiveWorkbook.Close

If you have a look in your Task Manager you will probably find loads of instances of Excel open that you can only close by killing their processes.

Try changing your code to:

    fsName = xlApp.Application.GetSaveAsFilename
    xlApp.ActiveWorkbook.SaveAs fileName:=fsName
   xlApp.ActiveWorkbook.Close