MS Project宏将不会在连续运行中打开Excel文件

Question

我正在编写一个带有目标打开和Excel文件的宏。它在每隔一次运行宏时都能成功工作。下面的程序在第一次运行时成功打开了该文件，但不会在第二次运行。然后它将进行第三次尝试，但不会进行第四次。然后循环重复。

Sub OpenExcelFile()
Set pj = ActiveProject
Set xlApp = New Excel.Application
xlApp.Visible = True
CurrPath = Application.ActiveProject.Path
Dim FD As FileDialog
Set FD = xlApp.FileDialog(msoFileDialogFilePicker)
With FD
.InitialFileName = "N:CorporateP2P"
.Title = "Select Excel File"
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsm*"
.Show
End With
Dim xlFile As String
xlFile = FD.SelectedItems(1)
MyFileName = xlFile
If MyFileName <> False Then
Workbooks.Open FileName:=MyFileName
End If

FileNameFromPath = Right(MyFileName, Len(MyFileName) - 
InStrRev(MyFileName, ""))
Set xlBook = xlApp.Workbooks(FileNameFromPath)
xlBook.Activate

End Sub

Answer 1

您有一个不合格的Workbooks.Open调用。我建议你改变这个：

If MyFileName <> False Then
Workbooks.Open FileName:=MyFileName
End If

FileNameFromPath = Right(MyFileName, Len(MyFileName) - 
InStrRev(MyFileName, ""))
Set xlBook = xlApp.Workbooks(FileNameFromPath)

到此：

If MyFileName <> False Then
Set xlBook = xlApp.Workbooks.Open FileName:=MyFileName
End If