Calculation not a method of Application or ActiveWorkbook

Question

I writing this code in MS-Project, but I thought I included all the necessary libraries to interface with Excel.

Here's what I have tried:

Sub OpenBackupFile()
    Titler = ActiveProject.CustomDocumentProperties("Title").Value
    BackupFile = "C:\POAMLogs\" & Titler & ".xlsx"


    'Set ExcelBackerp = CreateObject("Excel.Application")
    Set ExcelBackerp = New Excel.Application
    With ExcelBackerp

        .Calculation = xlCalculationManual

        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Workbooks.Open BackupFile

    End With

End Sub

No matter what I do I get, Method "Calculation" of Object "_Application" Failed I can get intellisense to give me the first one as an option, but in the second one 'calculation' doesn't show up in intellisense.

Answer 1

I can get intellisense to give me the first one as an option, but in the second one 'calculation' doesn't show up in intellisense.

That is because .Calculation is not a property of Workbook. It is of Application

You can use ExcelBackerp.Calculation = xlCalculationManual but not ExcelBackerp.ActiveWorkbook.Calculation = xlCalculationManual

Also remember if you ever use latebinding then change xlCalculationManual to -4135

You are getting an error because you are trying to set the calculation when there is no workbook.

Try this and it will work

Set ExcelBackerp = New Excel.Application
ExcelBackerp.Workbooks.Add   
ExcelBackerp.Calculation = xlCalculationManual

So in your code open the workbook first and then set the calculation. :)

Sub OpenBackupFile()
    Titler = ActiveProject.CustomDocumentProperties("Title").Value
    BackupFile = "C:\POAMLogs\" & Titler & ".xlsx"

    'Set ExcelBackerp = CreateObject("Excel.Application")
    Set ExcelBackerp = New Excel.Application
    With ExcelBackerp
        .Workbooks.Open BackupFile
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
End Sub

or better, declare your variables/objects. will make your life easier :)

Sub OpenBackupFile()
    Titler = ActiveProject.CustomDocumentProperties("Title").Value
    BackupFile = "C:\POAMLogs\" & Titler & ".xlsx"

    Dim ExcelBackerp As Excel.Application
    Dim wb As Excel.Workbook

    Set ExcelBackerp = New Excel.Application

    With ExcelBackerp
        Set wb = .Workbooks.Open(BackupFile)

        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    '~~> Work with the workbook here
    With wb

    End With
End Sub