Seemingly random fail during loop, Method ‘Open’ of object ‘Workbooks’

[philscrase]

Hi,

I seem to be running into a randomly generated error with the Application.Workbooks.Open but what really stumps me is that the error occurs on the opening of the file but each time it happens it occurs on a different attempt at opening a file. For example, this code has run successfully through its loop opening 30 files but has failed on file number 31 of 32. Previously this error occurred on file number 6, before that it was number 10 and so on. Each time it’s a different file.

The error message that pops up is Run-time error ‘1004’: Method ‘Open’ of object ‘Workbooks’ failed.

The line of failure is ‘Application.Workbooks.Open (ThisWorkbook.Path & "" & ListBox1.List(l - 1))’ – however, once I go into debug mode and initiate the step through it continuing from where the error occurred it’s totally fine and continues through the code as it should.

This code is part of a model which has been running for years. It was originally created in Excel 2003 and has been used in that environment without error. This error seems to be happening since I run it in Excel 2016. It’s a model that I developed for another team so I don’t use it every week like they do, but when a change is required then I use Excel 2016 to make changes. I’m pretty sure I’ve made changes in Excel 2016 to this model previously and it has run fine until now.

I’ve researched the phenomena where the fact that later versions of Excel use individual windows for Excel documents rather than the single window of old, which I thought might be the problem hence me entering the Application.Wait lines. Considering the fact that when it does error, by the time I press F8 in the debugger its fine I thought it might need a few seconds to catch up. Although there have been a couple of occasions where I’ve pressed F8 and its produced the same error but if I press F8 for a third time it passes through fine.

My line ‘Windows(ESCREPORT).Activate’ is making Excel select my report as the active window as an amateur attempt at workaround for having multiple excel windows open but it’s errored again and I’m at a loss for what is causing it.

I know this is a bit of an essay and it’s not an easily repeatable piece of code for anyone wishing to test it but I wondered if there was anything I’ve not discovered about the use of Workbooks.Open that will cause a random fail that seems to just need a bit of time every now and then during the execution?

My section of code where the error occurs is written as:

For l = 1 To ListBox1.ListCount
Windows(ESCREPORT).Activate
Application.Wait Now + TimeValue("00:00:03")
On Error GoTo OpenError
RunMode = "ACTIVE"

    If ListBox1.Selected(l - 1) Then Application.Workbooks.Open (ThisWorkbook.Path & "" & ListBox1.List(l - 1))

OpenError:
If RunMode = "ACTIVE" Then
Application.StatusBar = "Error opening " & (ThisWorkbook.Path & "" & ListBox1.List(l - 1)) & ", retrying in 20 seconds"
Application.Wait Now + TimeValue("00:00:20")
Application.Workbooks.Open (ThisWorkbook.Path & "" & ListBox1.List(l - 1))
End If
On Error GoTo 0

Any thoughts, help or guidance would be much appreciated

Kind Regards,
Phil