CODEWITHSUNDEEP
excelvbams-project
John Sullivan
John Sullivan

Reputation: 11

MS Project macro will not open Excel File in Consecutive Runs

I am writing a macro with the goal opening and Excel file. It works successfully on every other run of the macro. The below opens the file successfully on the first run, but won't run a second time. It will then run on a third try, but not a fourth. The cycle then repeats.

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

Upvotes: 0

Views: 97

Answers (1)

Rory
Rory

Reputation: 34045

You have an unqualified Workbooks.Open call in there. I'd suggest you change this:

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


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

to this:

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

Upvotes: 1

Related Questions