Excel 2016 File Opening Macro Slow/ Failing

[steve the fish]

Does anyone know why the following macro works fine in 2010 but is slow and eventually fails with memory error in 2016? It will open/ close approximately 7 books a second in 2010 but only maybe 1 a second in 2016. It has to open approx 8000 workbooks in total.

FolderPath = "\\whatever\"
Filename = Dir(FolderPath & "*.xls*")
    
Do While Filename <> ""
    Set wb = Workbooks.Open(FolderPath & Filename, ReadOnly:=True)
    wb.Close False
    Set wb = Nothing
    Filename = Dir
Loop

 

[Yongle]

Your macro works without incident for me with Excel 2016 and some pretty hefty files
- opened over a hundred files quickly

Your problem could be a memory issue caused by one of your files - But which one may take some working out!
See this article from Microsoft https://support.microsoft.com/en-gb...-in-the-32-bit-edition-of-excel-2013-and-2016

Could be a corruption somewhere on your PC - if possible test it on a different PC

I topped and tailed your code - I expect you already have the same.
It made a massive difference to the speed and that may avert code failure

Private Sub Workbook_Open()
[COLOR=#ff0000]Application.ScreenUpdating = False: Application.Calculation = xlManual[/COLOR]
    FolderPath = "j:\folder\subfolder\"
    Filename = Dir(FolderPath & "*.xls*")
    Do While Filename <> ""
        Set wb = Workbooks.Open(FolderPath & Filename, ReadOnly:=True)
        wb.Close False
        Set wb = Nothing
        Filename = Dir
    Loop
[COLOR=#ff0000]Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic[/COLOR]
End Sub

What other process is happpening? Are you simply updating values in the master file?

 

[JackDanIce]

Hi Steve,

I too tried to look through a few sites and nothing specific to your problem. Aside from the Application changes (suggested in #3 , which I suspect you're aware of), the only other difference I read on one site was suggesting include DoEvents before opening file and after closing file, if the file itself is trying to do anything immediately after opening/before closing.

However, this seems like it will add time/slow your code execution than improve, but if you want to try:

Set wb = Workbooks.Open(FolderPath & Filename, ReadOnly:=True)
DoEvents
wb.Close False
DoEvents

I would also remove the line Set wb = Nothing and place outside of the loop. You don't need to keep clearing the variable inside the loop, given how it's being used.

The link in #3 suggests the "Why" but as @Yongle suggests if it's a memory issue caused by one of the files, you could start by opening "groups" of files (e.g. the largest 10%, then the next largest 25% etc) and see if that helps to pinpoint issues with a specific file or not. Alternatively you could use a timer and debug.print to see if you can isolate any files that take longer to open/close than norm:

Dim xTimer As Variant

Do While Filename <> ""
xTimer = Timer
Set wb = Workbooks.Open(FolderPath & Filename, ReadOnly:=True)
DoEvents
wb.Close False
DoEvents
Debug.Print wb.Name & " time: " & Timer - xTimer
Loop

If this code fails to run, you can search for timing code online - I'm including it from memory but may have the syntax wrong.

I'm guessing you're doing more than just opening and closing a file, there may be other optimisations that help but it's just guesswork at this stage! Good luck finding a faster solution

Finally, and I doubt this will make any noticable difference, but you can try using:

Filename = Dir(FolderPath & "*.xls*")
Do While Lenb(Filename)
''your code
Loop

 

[steve the fish]

Cheers for the replies. It's odd. These aren't large workbooks. From the first workbook it attempts to open 2016 is slower. It always fails eventually but randomly. I have another machine with 2010 on so it isn't a major problem. I thought perhaps it was an issue with the way 2016 gets the file from the server. I just don't know. It's slow if I use fso as well so it's not just dir being outdated.

 

[JackDanIce]

Maybe run some code that lists every file in the folder, then separately run code to only open (say) 10 files max per macro run? If it always fails eventually, may suggest memory issues or Excel 2016 running out of resources or even an o/s related conflict.

I currently use 2013 at home so wouldn't really be able to create your set up. Be interested to see if you find solution (and don't mind posting back) or someone else does.

 

[RoryA]

If you are opening workbooks that were last saved in a version older than 2016, each one of them will be recalculated as it's opened, which might explain the slow performance.

What is the point of the code, given that you don't change or save any of the workbooks?