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:\Corporate\P2P"
    .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