GetObject Performance Decline in Office 365

  • 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:

    Closing the file:

    Set rRange = Nothing
    .Close 0
    Set wbOpenBook = Nothing

    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?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!