How to set calculation mode to manual when opening an excel file?

Question

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

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?

Answer 1

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

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