Slow vba with opening 60 files

[NickvdB]

Hi,

Thanks to help via multiple ways I have a VBA setup in the situation I have three files already open. For which two are used to provide input to 60 separate files, and the 60 separate files provide input to the third file. All are office 365 sharepoint files. With the three files open, the VBA opens the 60 files one by one, recalculated, and updating the values from and to the other three files and then closes each file before opening the next of the 60. However the VBA does take me 10 minutes or more each time I run it. And sadly due to company decissions running Excel in 32 bit. Does anybody have an idea on how to optimize the following code? I think it will also be too risky to disable calculations, than open 60 files, restart calculations, after calculations close all 60 files? But open to any suggestions!

Sub Update()
Dim lr As Long
Dim i As Integer
Dim WBSsource As Workbook
Dim FileNames As Variant
Dim msg As String
Application.ScreenUpdating = False
Application.EnableEvents = False
With ThisWorkbook.Sheets("Macro")
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
FileNames = .Range("a1:a" & lr).Value
End With
For i = LBound(FileNames, 1) To UBound(FileNames, 1)
On Error Resume Next
If FileNames(i, 1) Like "*.xlsm" Then
Set WBSsource = Workbooks.Open(FileNames(i, 1), _
ReadOnly:=False, _
UpdateLinks:=True, _
Password:="")
If Err = 0 Then
With WBSsource

ActiveWorkbook.Save
ActiveWindow.Close
End With
Else
msg = msg & FileNames(i, 1) & Chr(10)
On Error GoTo 0
End If
End If
Set WBSsource = Nothing
Next i
If Len(msg) > 0 Then
MsgBox "The Following Files Could Not Be Opened" & _
Chr(10) & msg, 48, "Error"
End If
ActiveWorkbook.AutoSaveOn = True
Application.ScreenUpdating = True
Application.EnableEvents = True
MsgBox "All files have been updated"
End Sub

 

[6StringJazzer]

It looks like you marked your code as a quote instead of code, so it's a little hard to read without any indentation.

I'm not sure how this can be optimized because you aren't doing anything with the files. You just open them and immediately close them.

recalculated, and updating the values from and to the other three files

I don't see this happening in the code.

You are using a With that doesn't make sense but it shouldn't cause a performance problem.


Here is your code reformatted for readability.

            With WBSsource
               ActiveWorkbook.Save
               ActiveWindow.Close
            End With

You should just do this:

            WBSsource.Close

Here is your complete original code:

Sub Update()

   Dim lr As Long
   Dim i As Integer
   Dim WBSsource As Workbook
   Dim FileNames As Variant
   Dim msg As String
   
   Application.ScreenUpdating = False
   Application.EnableEvents = False
   
   With ThisWorkbook.Sheets("Macro")
      lr = .Cells(.Rows.Count, "A").End(xlUp).Row
      FileNames = .Range("a1:a" & lr).Value
   End With
   
   For i = LBound(FileNames, 1) To UBound(FileNames, 1)
      On Error Resume Next
      If FileNames(i, 1) Like "*.xlsm" Then
         Set WBSsource = Workbooks.Open(FileNames(i, 1), _
                                        ReadOnly:=False, _
                                        UpdateLinks:=True, _
                                        Password:="")
         If Err = 0 Then
            With WBSsource
               ActiveWorkbook.Save
               ActiveWindow.Close
            End With
         Else
            msg = msg & FileNames(i, 1) & Chr(10)
            On Error GoTo 0
         End If
      End If
      
      Set WBSsource = Nothing
   Next i
   
   If Len(msg) > 0 Then
      MsgBox "The Following Files Could Not Be Opened" & _
      Chr(10) & msg, 48, "Error"
   End If
   
   ActiveWorkbook.AutoSaveOn = True
   Application.ScreenUpdating = True
   Application.EnableEvents = True
   MsgBox "All files have been updated"

End Sub

 

[NickvdB]

1) Sorry on the quotes
2) Just by having the files open at the same time the linked cells are automatically updated. That is why I can just open and close them
3) Because of being sharepoint files with autosave I could already improve some speed by removing the Activeworkbook.save
4) I think your suggestion on wbssource.close also helped
5) I think it went all and all down with 1minute, but still 11minutes required to just open and close 60 files
6) When I have screenupdating on I see it takes quite a long time to open the file and change the user account at top right to my picture, so feels like it logs in like every file that is opened
7) Same goes for rather long period on updating workflow status, which for sure has to do with autosave being on
8) That is why it got me thinking in direction of disable auto calc, open 60 files from list like it is done now, enable auto calc and force calculation, close all 60 files, would that work? Or would it crash my computer

 

[NickvdB]

To add, when switching autosave off on the main file running the macro from, it saves another minute.

 

[6StringJazzer]

Is it the 60 files you want to update by opening them, or you want to update the three files by opening the 60?

It takes a relatively long execution time to open and close files, and if you're using SharePoint it has to do this over the network. 60 files in 10 minutes is 10 seconds to open and close each file, which is longer than I would guess but still not crazy.

I guess I would look at the total process here. What is in those three files that require to actually open the other files to update the data? I'm wondering if we can set them up to update without opening files.

 

[NickvdB]

Is it the 60 files you want to update by opening them, or you want to update the three files by opening the 60?

It takes a relatively long execution time to open and close files, and if you're using SharePoint it has to do this over the network. 60 files in 10 minutes is 10 seconds to open and close each file, which is longer than I would guess but still not crazy.

I guess I would look at the total process here. What is in those three files that require to actually open the other files to update the data? I'm wondering if we can set them up to update without opening files.

The three files are A, B and C.
File C is running the macro and pulling data from file B and all 60 files
All 60 files pull data from A and B, and provide input to B
Hope this makes sense.

I was also just dividing the 10 minutes by 60 files, and not that bad indeed. So if you say, not bad and deal with it, other than going to 64 bit excel. However I think most "seconds" are wasted with the sharepoint flow.

 

[6StringJazzer]

When I did a test it was about 1 second per file but that was on a local SSD. Going over the network would take much longer but I don't have access to SharePoint for testing it.

Going to 64-point won't help. That just gives you a bigger address space in memory. It doesn't really speed anything up unless you are doing something that requires a very large memory footprint. It would have no discernible effect on opening/closing files.

 

[NickvdB]

Ok understood. Thanks for your time and help, much appreciated!!!