Excel delay with mepped network drive

Hi

We have a strange behaviour with one of our Excel 2010 xlsm files with a file size of 30MB and hundreds of formulas in it.
If we open the file on a server 2008 R2 share, the calculation of all the formulas takes 140 seconds, so we moved the file in a first step to the local hard disk but the duration is exactly the same. But if we disconnect one of our network drives, it takes only 6 seconds!
So it became apparent, that it must have some references in the file to this network drive X:. But surprisingly, there is even not one external reference in the file.

So we started ProcMon and were surprised again. When we start the recalculation of the sheet, we get thousands of "QueryOpen" and "CreateFile" for each known Excel file format in the same folder where the file is executed on.

12:46:53.3919715,"EXCEL.EXE","2716","CreateFile","C:\Users\admin.company\Documents\_2013.xls","NAME NOT FOUND","Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a"
12:46:53.3920543,"EXCEL.EXE","2716","QueryOpen","C:\Users\admin.company\Documents\_2013.htm","FAST IO DISALLOWED",""
12:46:53.3921173,"EXCEL.EXE","2716","CreateFile","C:\Users\admin.company\Documents\_2013.htm","NAME NOT FOUND","Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a"
12:46:53.3921994,"EXCEL.EXE","2716","QueryOpen","C:\Users\admin.company\Documents\_2013.lnk","FAST IO DISALLOWED",""
12:46:53.3922624,"EXCEL.EXE","2716","CreateFile","C:\Users\admin.company\Documents\_2013.lnk","NAME NOT FOUND","Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a"
12:46:53.3923654,"EXCEL.EXE","2716","QueryOpen","C:\Users\admin.company\Documents\_2013.xlsx","FAST IO DISALLOWED",""
12:46:53.3924301,"EXCEL.EXE","2716","CreateFile","C:\Users\admin.company\Documents\_2013.xlsx","NAME NOT FOUND","Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a"
12:46:53.3924441,"EXCEL.EXE","2716","CloseFile","C:\Windows\winsxs\x86_microsoft.windows.common-controls_6595b64144ccf1df_6.0.7601.17514_none_41e6975e2bd6f2b2","SUCCESS",""
12:46:53.3925122,"EXCEL.EXE","2716","QueryOpen","C:\Users\admin.company\Documents\_2013.xlsm","FAST IO DISALLOWED",""
12:46:53.3925810,"EXCEL.EXE","2716","CreateFile","C:\Users\admin.company\Documents\_2013.xlsm","NAME NOT FOUND","Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a"
12:46:53.3926652,"EXCEL.EXE","2716","QueryOpen","C:\Users\admin.company\Documents\_2013.xlsb","FAST IO DISALLOWED",""
12:46:53.3927291,"EXCEL.EXE","2716","CreateFile","C:\Users\admin.company\Documents\_2013.xlsb","NAME NOT FOUND","Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a"
12:46:53.3928195,"EXCEL.EXE","2716","QueryOpen","C:\Users\admin.company\Documents\_2013.xls","FAST IO DISALLOWED",""
12:46:53.3928824,"EXCEL.EXE","2716","CreateFile","C:\Users\admin.company\Documents\_2013.xls","NAME NOT FOUND","Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a"
12:46:53.3929659,"EXCEL.EXE","2716","QueryOpen","C:\Users\admin.company\Documents\_2013.htm","FAST IO DISALLOWED",""
12:46:53.3930299,"EXCEL.EXE","2716","CreateFile","C:\Users\admin.company\Documents\_2013.htm","NAME NOT FOUND","Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a"
12:46:53.3931179,"EXCEL.EXE","2716","QueryOpen","C:\Users\admin.company\Documents\_2013.lnk","FAST IO DISALLOWED",""
12:46:53.3931849,"EXCEL.EXE","2716","CreateFile","C:\Users\admin.company\Documents\_2013.lnk","NAME NOT FOUND","Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a"
12:46:53.3932845,"EXCEL.EXE","2716","QueryOpen","C:\Users\admin.company\Documents\_2013.xlsx","FAST IO DISALLOWED",""
12:46:53.3933488,"EXCEL.EXE","2716","CreateFile","C:\Users\admin.company\Documents\_2013.xlsx","NAME NOT FOUND","Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a"
12:46:53.3934327,"EXCEL.EXE","2716","QueryOpen","C:\Users\admin.company\Documents\_2013.xlsm","FAST IO DISALLOWED",""
12:46:53.3934984,"EXCEL.EXE","2716","CreateFile","C:\Users\admin.company\Documents\_2013.xlsm","NAME NOT FOUND","Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a"
12:46:53.3935832,"EXCEL.EXE","2716","QueryOpen","C:\Users\admin.company\Documents\_2013.xlsb","FAST IO DISALLOWED",""
12:46:53.3936472,"EXCEL.EXE","2716","CreateFile","C:\Users\admin.company\Documents\_2013.xlsb","NAME NOT FOUND","Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a"
12:46:53.3937335,"EXCEL.EXE","2716","QueryOpen","C:\Users\admin.company\Documents\_2013.xls","FAST IO DISALLOWED",""
12:46:53.3937971,"EXCEL.EXE","2716","CreateFile","C:\Users\admin.company\Documents\_2013.xls","NAME NOT FOUND","Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a"

Open in new window


The VBA code is pretty simple for this workbook and I don't find anything, what could end in such a behaviour from Excel.

Option Explicit
Global Control_References(1 To 26, 1 To 2)


Sub Initial_SetUp()
'
    Dim SheetName As String
'
    
    
    Sheets("Data").Select
    Cells.Find(What:="Sheets with Manual Calculation", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Range("A1").Select
    While ActiveCell <> ""
        SheetName = ActiveCell.FormulaR1C1
        Worksheets(SheetName).EnableCalculation = False
        ActiveCell.Offset(1, 0).Range("A1").Select
    Wend
    
End Sub


Sub Calculate()
    Dim OpenTime As Variant
    
    OpenTime = Timer
      
    ActiveSheet.EnableCalculation = True
    ActiveSheet.EnableCalculation = False
    
    MsgBox "Execution time: " & Round(Timer - OpenTime, 3) * 1000 & " Milliseconds"

End Sub


Sub Select_Parameters()

    Dim x As Single

    Control_References(1, 1) = "D14"
    Control_References(1, 2) = "LE_CH"
    Control_References(2, 1) = "D15"
    Control_References(2, 2) = "LE_RO"
    Control_References(3, 1) = "D16"
    Control_References(3, 2) = "LE_GGG"
    Control_References(4, 1) = "D17"
    Control_References(4, 2) = "LE_DE"
    Control_References(5, 1) = "D18"
    Control_References(5, 2) = "LE_IT"
    Control_References(6, 1) = "D19"
    Control_References(6, 2) = "LE_FR"
    Control_References(7, 1) = "D20"
    Control_References(7, 2) = "LE_UK"
    Control_References(8, 1) = "D21"
    Control_References(8, 2) = "LE_US"
    Control_References(9, 1) = "D22"
    Control_References(9, 2) = "LE_MX"
    Control_References(10, 1) = "D23"
    Control_References(10, 2) = "LE_CN"
    Control_References(11, 1) = "D24"
    Control_References(11, 2) = "LE_JP"
    
    Control_References(12, 1) = "D26"
    Control_References(12, 2) = "A_EU"
    Control_References(13, 1) = "D27"
    Control_References(13, 2) = "A_NA"
    Control_References(14, 1) = "D28"
    Control_References(14, 2) = "A_LA"
    Control_References(15, 1) = "D29"
    Control_References(15, 2) = "A_MX"
    Control_References(16, 1) = "D30"
    Control_References(16, 2) = "A_AP"
    
    Control_References(17, 1) = "D32"
    Control_References(17, 2) = "MGM_Trade"
    Control_References(18, 1) = "D33"
    Control_References(18, 2) = "MGM_AU"
    Control_References(19, 1) = "D34"
    Control_References(19, 2) = "MGM_DE"
    Control_References(20, 1) = "D35"
    Control_References(20, 2) = "MGM_IT"
    Control_References(21, 1) = "D36"
    Control_References(21, 2) = "MGM_FR"
    Control_References(22, 1) = "D37"
    Control_References(22, 2) = "MGM_UK"
    Control_References(23, 1) = "D38"
    Control_References(23, 2) = "MGM_US"
    Control_References(24, 1) = "D39"
    Control_References(24, 2) = "MGM_MX"
    Control_References(25, 1) = "D40"
    Control_References(25, 2) = "MGM_CN"
    Control_References(26, 1) = "D41"
    Control_References(26, 2) = "MGM_JP"
        
    For x = 1 To 26
    
        Select Case Range(Control_References(x, 1)).Value
            Case 1
                Parameters_Selection.Controls(Control_References(x, 2)).Enabled = True
                Parameters_Selection.Controls(Control_References(x, 2)).Value = True
            Case 0
                Parameters_Selection.Controls(Control_References(x, 2)).Enabled = True
                Parameters_Selection.Controls(Control_References(x, 2)).Value = False
            Case "N/A"
                Parameters_Selection.Controls(Control_References(x, 2)).Value = False
                Parameters_Selection.Controls(Control_References(x, 2)).Enabled = False
        End Select
    
    Next
    
    Parameters_Selection.Show

End Sub

Open in new window


Really appreciate any kind of feedback.

Thx
LVL 1
Wayne7215Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

redmondbCommented:
Hi, Wayne7215.

Can you create a smaller, redacted version of the file and, assuming that it's still showing the same behaviour, post it here, please?

Thanks,
Brian.
0
Wayne7215Author Commented:
Hi Brian

Thanx for your suggestion, but no we can't, because it has very sensitive data inside.

BUT anyway, incredibly, but we solved the problem right now!
Since years we have customized the Office default path for all our users with a GPO to our default server path X:\ and this worked for years now without any problem! But if we change it back to C:\ the same Excel workbook calculation takes only 6 seconds instead of 140 seconds!!!!

In my opinion that's really a bug and should be fixed. Now all our users have to click to X: when they want to save a file.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
redmondbCommented:
Wayne7215,

Is this behaviour happening with other files? If so, please a redacted version of one of those.

Thanks,
Brian.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Wayne7215Author Commented:
Brian

We never have seen any problem with changing the default working folder to a server share with other files, only with this one Excel workbook. Sorry but as I told you, we can't share this file.

But thx for your help
0
redmondbCommented:
Wayne7215,

Yes, I understand that - hence my request for another file!

As this is the only file with this behaviour, it's more likely to be a problem with it than an Excel bug. Not a pleasant task, but I'd give serious consideration to doing a complete rebuild of the file.

Regards,
Brian.
0
Wayne7215Author Commented:
We were quicker than others
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.