我在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"的方法"Calculation"Failed我都可以得到intelligense,将第一个作为选项,但在第二个中,"Calculation"不会显示在intelligSense中。
我可以让intellisense给我第一个选项,但在第二个选项中,"计算"不会显示在intellisenses中。
这是因为.Calculation
不是Workbook
的属性。它是Application
您可以使用ExcelBackerp.Calculation = xlCalculationManual
,但不能使用ExcelBackerp.ActiveWorkbook.Calculation = xlCalculationManual
还请记住,如果您使用后期绑定,请将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