excel - 计算不是 Application 或 ActiveWorkbook 的方法

Question

我在 MS-Project 中编写了这段代码，但我认为我包含了所有必要的库来与 Excel 交互。

这是我尝试过的:

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

不管我做什么， 对象“_Application”的方法“计算”失败
我可以让智能感知给我第一个选项，但在第二个中，“计算”不会出现在智能感知中。

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.

那是因为 .Calculation不是 Workbook 的属性.它是 Application
您可以使用 ExcelBackerp.Calculation = xlCalculationManual但不是 ExcelBackerp.ActiveWorkbook.Calculation = xlCalculationManual
还请记住，如果您曾经使用后期绑定(bind)，请更改 xlCalculationManual至 -4135
您收到错误是因为您在没有工作簿时尝试设置计算。

试试这个，它会工作

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

因此，在您的代码中首先打开工作簿，然后设置计算。 :)

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

或者更好，声明你的变量/对象。会让你的生活更轻松:)

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