K
kunkletown
Based upon my research on this topic I think the following is a bug in
Excel 2003...
In the current workbook, executing a VBA macro that opens a CSV file
causes the current workbook to be recalculated even though; the current
workbook is the only other open workbook AND the current workbook's
calculation option is set to MANUAL. This behavior is extremely
problematic if the workbook executing the VBA code has many formulas,
and thus the recalc takes a long time -- which of course is why you
would select the manual calculation option in the first place.
To reproduce this issue do the following:
1- Open the Excel application
2- Open a new workbook (now the only open workbook)
3- Add the following VBA code to the new workbook:
Private Sub OpenCsvFile()
Dim oNewWorkbook As Workbook
'prompt for file
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Filters.Clear
.Filters.Add "Comma delimited files", "*.csv", 1
.Show
'open as read-only and ignore read-only
Set oNewWorkbook = Workbooks.Open(.SelectedItems.Item(1), ,
True, , , , True)
End With
End Sub
4- Set the new workbook's calculation mode to Manual (From Excel's menu
select Tools/Options - on the Calculation tab, select "Manual" and do
not check the "Recalculate before save box"). Note that this could be
done via VBA code, but I wanted to eliminate that variable in case
someone would raise the concern that setting the
Application.Calculation property in the VBA code was the cause of the
issue (i.e. Application.Calculation = xlCalculationManual).
5- Enter "1" in cell A1 of "Sheet1" of the new workbook
6- Enter "1" in cell A2
7- Enter "=A1+A2" (a formula) in cell A3
8- Now change cell A1's value to "3" (notice the formula in cell A3
still evaluates to "2" since the workbook is in manual calc mode)
9- Execute the VBA code "OpenCsvFile" (above) and select any CSV file
to be opened when prompted.
10- The VBA code will open the selected CSV file
After running the VBA code, look at cell A3 and notice the formula now
shows a value of "4" (the new A1 and A2 values [1+3], instead of the
original values [1+1]). This proves that the workbook has been
recalculated (by Excel) as a result of running the VBA code. Notice
the VBA code does not request the recalc (i.e. Application.Calculate).
In the code above, the recalculation occurs when the CSV file is opened
with this line of VBA code:
Set oNewWorkbook = Workbooks.Open(.SelectedItems.Item(1), , True, , , ,
True)
Others have complained about this behavior in this newsgroup, but
responses to their complaints have focused on changing calculation mode
to manual and/or the behavior of Excel's calc mode when multiple files
are open (i.e. subsequently opened files adopt the prior file calc
mode). Both of those suggested solutions are addressed/included in the
above example, but the issue still persists.
Any ideas?
Thanks!
Excel 2003...
In the current workbook, executing a VBA macro that opens a CSV file
causes the current workbook to be recalculated even though; the current
workbook is the only other open workbook AND the current workbook's
calculation option is set to MANUAL. This behavior is extremely
problematic if the workbook executing the VBA code has many formulas,
and thus the recalc takes a long time -- which of course is why you
would select the manual calculation option in the first place.
To reproduce this issue do the following:
1- Open the Excel application
2- Open a new workbook (now the only open workbook)
3- Add the following VBA code to the new workbook:
Private Sub OpenCsvFile()
Dim oNewWorkbook As Workbook
'prompt for file
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Filters.Clear
.Filters.Add "Comma delimited files", "*.csv", 1
.Show
'open as read-only and ignore read-only
Set oNewWorkbook = Workbooks.Open(.SelectedItems.Item(1), ,
True, , , , True)
End With
End Sub
4- Set the new workbook's calculation mode to Manual (From Excel's menu
select Tools/Options - on the Calculation tab, select "Manual" and do
not check the "Recalculate before save box"). Note that this could be
done via VBA code, but I wanted to eliminate that variable in case
someone would raise the concern that setting the
Application.Calculation property in the VBA code was the cause of the
issue (i.e. Application.Calculation = xlCalculationManual).
5- Enter "1" in cell A1 of "Sheet1" of the new workbook
6- Enter "1" in cell A2
7- Enter "=A1+A2" (a formula) in cell A3
8- Now change cell A1's value to "3" (notice the formula in cell A3
still evaluates to "2" since the workbook is in manual calc mode)
9- Execute the VBA code "OpenCsvFile" (above) and select any CSV file
to be opened when prompted.
10- The VBA code will open the selected CSV file
After running the VBA code, look at cell A3 and notice the formula now
shows a value of "4" (the new A1 and A2 values [1+3], instead of the
original values [1+1]). This proves that the workbook has been
recalculated (by Excel) as a result of running the VBA code. Notice
the VBA code does not request the recalc (i.e. Application.Calculate).
In the code above, the recalculation occurs when the CSV file is opened
with this line of VBA code:
Set oNewWorkbook = Workbooks.Open(.SelectedItems.Item(1), , True, , , ,
True)
Others have complained about this behavior in this newsgroup, but
responses to their complaints have focused on changing calculation mode
to manual and/or the behavior of Excel's calc mode when multiple files
are open (i.e. subsequently opened files adopt the prior file calc
mode). Both of those suggested solutions are addressed/included in the
above example, but the issue still persists.
Any ideas?
Thanks!