none
Opening excel files verry slow in windows 10 (tested on different pc's and windows and excel versions) RRS feed

  • Question

  • Can anybody help me find a reason and better even a solution to the question why opening excel files is a lot slower in windows 10 vs windows 7 vs windows server 2008/2016.

    I have a main macro file to open other excel files, price inquiries. Those price inquiries are not big sized excel files. The macros do a bunch of checks and get data from an sql server.

    When I run those macros on different pc's with different versions of windows and different versions of excel I get a big spread in results in time to open those price inquiries (and do some checks and run a few sql queries).

    I have been monitoring those times for a while now and the results can be seen in a chart.

    In this chart you can see that using windows 7 (and excel 2010) the average time to open a price inquiry is about 5 seconds.

    Using windows 10 the time varies between 5 and 60s (up to 90s). The excel version does not really have an influence on the time.

    Does anybody know how or where to check what part in the whole process is slower between win10 and others?

    All files have extension .xls

    Friday, March 13, 2020 2:20 PM

All replies

  • Hi Frank,

    I'd like to know what the X-axis is.
    According only to the description, we can not guess what causes the difference between Windows (and Excel ) version, since hardware spec is not provided.  And if the Excel workbook is on the network storage, we need to consider the difference of network speed/transfer rate.

    Regards,

    Ashidacchi -- http://hokusosha.com

    Saturday, March 14, 2020 12:13 AM
  • Thank you Ashidacchi for your reply.

    The X-axis is just the amout of tests that have been done (more than 2500 so far).

    The hardware specs have not been provided because I think that this is of less to no influence (I am not a specialist so I could be wrong). The computers with windows 7 are much older than the computers with windows 10, which will mean that the hardware specs are better for the newer computers (win10) than the older computers (win7).
    In the resluts however this does not seem to be of any influence. As you can see that almost all of the time results for pc's with windows 7 (green triangles) the time is about 5 seconds, as for the pc's with windows 10 (but with better hardware specs) there is a very big scatter range.

    All these results come from different pc's. 4 pc's with windows 7 and 5 pc's with windows 10 and 8 pc's using citrix (win server 2008 or 2016).
    The way of working is for all pc's the same: The main macro file is on a network drive and also all of the inquiry files are on that same network. The main macro file is just 1 file that everybody opens as read only. The inquiry files are all in the same network folder (only 1 folder).

    It is striking that almost all resluts from pc's using citrix (win server 2008 or 2016) AND pc's with windows 7 have the same low opening time (about 5 seconds), as for all pc's using windows 10 results are very unstable, but almost never even close to 5 seconds.

    All pc's are located in the same office and are connected in the same way to the network (and should have the same network speed/transfer rate I would think).

    Any help on this matter is appreciated

    Monday, March 16, 2020 9:32 AM
  • Hi Frank Van Eygen,

    I suppose OS version (Win.7 or Win.10), hardware spec (especially storage type, RAM, CPU) and network traffic have an effect on PC performance.

    Generally speaking, SSD is much faster than HDD, and Windows 10 requires more RAM than Windows 7, and the time to open an Excel file (I think it has macro) depends on network traffic (even if NIC/network speed is the same, much traffic makes PC's I/O slow).

    I'm not sure if I can understand Citrix, but if it means a virtual machine running on Windows Server, it could faster or slower than a physical one. It would depend on resources which are given to a virtual PC.

    Anyway, I'd like to know about PC's environments.  Regards,

    Ashidacchi -- http://hokusosha.com

    Monday, March 16, 2020 10:13 AM
  • Could you perhaps provide some VBA code to get the info you would need?

    Something I found was this:

    Declare Function GetCurrentProcessId Lib "kernel32" () As Long
    Function GetMemUsage()
      ' Returns the current Excel.Application
      ' memory usage in MB
      Set objSWbemServices = GetObject("winmgmts:")
      GetMemUsage = objSWbemServices.Get( _
        "Win32_Process.Handle='" & _
        GetCurrentProcessId & "'").WorkingSetSize / 1024
      Set objSWbemServices = Nothing
    End Function

    With this way of working I could get the main macro to register all different kinds of parameters but I would need to know what parameters it is you need (I do not have an IT backround but VBA is kind of my thing).

    Monday, March 16, 2020 10:48 AM
  • Hi,

    Before thinking about VBA code, please provide/explain what you want to do with macro.

    Regards,


    Ashidacchi -- http://hokusosha.com

    Monday, March 16, 2020 12:25 PM
  • The problem is that the colleagues who work on pc's with windows 10 (= new pc's) that those pc's take longer time to open excel files (with or without a VBA macro). These colleagues process about 1 price inquiry excel file each 5 minutes. So if they have to wait 5 sceonds or 1 minute for a price inquiry excel file to open, it makes a lot of difference.
    I need to find a solution for this problem.

    The way I gather this information about the windows versions, times to open the files and other types of data is with the main macro they already use to open and process those price inquiry excel files.

    I can use that same main macro file to get data on all of the pc's their memory satuts, transfer rates etc but I would need to know what data to get in order to find a solution.

    It is not possible for me to check each pc for the memory satuts, transfer rates etc myself, that is why I want to use the main macro file.

    Monday, March 16, 2020 12:57 PM
  • Hi Frank,

    In advance, sorry, I have no idea for collecting resources of network computers with VBA. (For that purpose, I could write VB.NET code and cannot with Excel VBA.)

    To make my understanding clear, I've drawn a diagram.

    I cannot understand why the Main Macro file is on the network that is used/shared by many computers. So, I suppose this diagram would be wrong. 

    I've shared the diagram (Diagram.xlsm) via OneDrive. Could you correct it or my understanding?

    Regards,


    Ashidacchi -- http://hokusosha.com



    • Edited by Ashidacchi Tuesday, March 17, 2020 5:49 AM typo
    Tuesday, March 17, 2020 5:48 AM
  • Hello Ashidacchi,

    Your diagram looks ok.

    Citrix is software that lets you run applications on a remote computer but interact with those applications on your local computer. It also lets you access a remote computer as if you were sitting at it, but inside a window.

    The reason everybody uses the main macro from the server is that I do a lot of updating to that file so everybody needs to have the latest version of that file and not an old copy from a few days/weeks ago. In Excel this file is opened localy as a read only copy from the network.

    All pc's running on windows 10 need more time to open the inquiry files and none of the other pc's, but all pc's work in the same way and use the same network and LAN. So why win10 pc's are slower I can not understand.

    Tuesday, March 17, 2020 9:51 AM
  • Hi Frank,

    Thank you for your detailed information.
    I understand all computers (both Win.7 and Win.10) execute the same Excel file locally.  (The source Excel file is in the server).

    If all computers are using the inquiry files on a single server (on the same server), I guess the performance would depends mainly on the difference of RAM size.
    Do the Windows 10 computers have more than 4 GB RAM?

    I don't know how large the Excel file-size (with macro) is. Opening an Excel file means loading it in RAM. I suspect RAM on Win.10 PCs is not sufficient. 

    About the very big scatter range in Win.10 PCs, I guess Windows Update is running in the background.  Windows Update sometimes makes a PC heavy. 

    Sorry, that's all I can say.

    Regards,

    Ashidacchi -- http://hokusosha.com

    Tuesday, March 17, 2020 10:37 AM
  • Hi Ashidacchi,

    All win10 pc's have 8GB RAM and have a SSD.

    Wednesday, March 18, 2020 9:31 AM
  • Hi,

    Please provide version (e.g. 2013, 2016, 2019) and architecture (32-bit or 64-bit) of Excel both in Win.7 and Win.10.
    Do they use Office 365?

    Regards,

    Ashidacchi -- http://hokusosha.com

    Thursday, March 19, 2020 2:40 AM