打开Excel文件时如何将计算模式设置为手动？ [英] How to set calculation mode to manual when opening an excel file?

问题描述

我的excel文件包含很多公式，因此，我希望它在打开时立即将计算模式设置为手动。否则计算自动开始，我必须等待几个小时。我发现这个页面：

My excel file contains a lot of formulas and I therefore want it to set calculation mode to manual as soon as it is opened. Otherwise calculation starts automatically and I have to wait for hours. I found this page:

http：// excel.tips.net/T001988_Forcing_Manual_Calculation_For_a_Workbook.html

应该能够做到这一点。但是它不适用于我的excel文件。它表示在VBA代码中的ThisWorkbook一节中，应输入以下代码：

which should be able to do the trick. However it's not working for my excel-file. It states that in the VBA-code, in the section "ThisWorkbook", the following code should be entered:

Private Sub Workbook_Open()
    Application.Calculation = xlManual
    Application.CalculateBeforeSave = False
End Sub

正如指出的那样，它在我的情况下不起作用。有人有替代解决方案吗？

As pointed it out, it doesn't work in my case. Does someone have an alternative solution?

提前感谢

推荐答案

最好的方法是在与您要打开的文件相同的文件夹中创建一个名为launcher.xlsm的Excel。在启动器文件中将以下代码放在Workbook对象中，但将常量 TargetWBName 设置为要打开的文件的名称。

The best way around this would be to create an Excel called 'launcher.xlsm' in the same folder as the file you wish to open. In the 'launcher' file put the following code in the 'Workbook' object, but set the constant TargetWBName to be the name of the file you wish to open.

Private Const TargetWBName As String = "myworkbook.xlsx"

'// First, a function to tell us if the workbook is already open...
Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

Private Sub Workbook_Open()
    'Check if our target workbook is open
    If WorkbookOpen(TargetWBName) = False Then
        'set calculation to manual
        Application.Calculation = xlCalculationManual
        Workbooks.Open ThisWorkbook.Path & "\" & TargetWBName
        DoEvents
        Me.Close False
    End If
End Sub

将常量TargetWBName设置为要打开的工作簿的名称。
此代码将简单地将计算转换为手动，然后打开该文件。然后，启动器文件将自动关闭。
*注意：如果您不想每次打开此文件时提示启用内容（取决于您的安全设置），您应该暂时删除me.close以防止它关闭，保存文件并将其设置为可信任，然后在重新保存之前重新启用me.close调用。或者，您可以在 Me.Close

Set the constant 'TargetWBName' to be the name of the workbook that you wish to open. This code will simply switch calculation to manual, then open the file. The launcher file will then automatically close itself. *NOTE: If you do not wish to be prompted to 'Enable Content' every time you open this file (depending on your security settings) you should temporarily remove the 'me.close' to prevent it from closing itself, save the file and set it to be trusted, and then re-enable the 'me.close' call before saving again. Alternatively, you could just set the False to True after Me.Close

这篇关于打开Excel文件时如何将计算模式设置为手动？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！