I have a tool that scrubs through many excel files on a network drive to pull data. I needed to allow users to select one folder after which the macro would import data from any excel file in any sub-folder. The solution I ended up implementing is as follows.
Opening the file:
'Pull list of all excel files sDir = sDir & "\*.xls*" sParentFolder = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & Chr(34) & sDir & Chr(34) & " /b/s").stdout.readall, vbCrLf), ":") dBarCount = 0 For j = 0 To UBound(sParentFolder) 'Initialize load bar dBarTotal = UBound(sParentFolder) + 1 + 180 LoadForm.LabelProgress.Width = 0 LoadForm.FrameProgress.Caption = "0%" LoadForm.Show On Error GoTo FileProblem Set wbOpenBook = GetObject(sParentFolder(j)) With Workbooks(wbOpenBook.Name)
Closing the file:
This has worked totally fine for years, but our laptops were recently upgraded to windows 10 and office 365 and performance has taken a major hit. What would take roughly 45 minutes to run in the past now takes 3 1/2 hours (if it even manages to successfully complete). I am pretty sure I do not have any memory links. I think if I did, the biggest one would be in relation to wbOpenBook listed above. Anyhow as I am trying to troubleshoot I noticed that windows search filter host and some of the other processes related to indexing seem to ramp up in CPU usage as the tool runs. Could that be driving some of the performance decline I am seeing or am I likely barking up the wrong tree?