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.
The VBA code is pretty simple for this workbook and I don't find anything, what could end in such a behaviour from Excel.
Really appreciate any kind of feedback.
Thx
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"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 SubReally appreciate any kind of feedback.
Thx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Wayne7215,
Is this behaviour happening with other files? If so, please a redacted version of one of those.
Thanks,
Brian.
Is this behaviour happening with other files? If so, please a redacted version of one of those.
Thanks,
Brian.
ASKER
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
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
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.
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.
ASKER
We were quicker than others
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.