Opening a Microsoft Project file with VBA

Question

I'm trying to open a Microsoft Project file via VBA and then copy some ranges to an excel workbook. But i'm getting an error in my code:

Run-time error '13': Type mismatch

On that command: Set mpp = myMPP.Application.FileOpen(FilenameMPP)

Sub project2xCell()

 Set myMPP = CreateObject("Msproject.Application")
 Set myXLS = CreateObject("Excel.application")

 FilenameMPP = OpenFileDialogMPP() 'Function to browse to a Microsoft Project file
 FilenameExcel = OpenFileDialogXLS() 'Function to browse to a Excel Workbook

 myMPP.Visible = True
 Set mpp = myMPP.Application.FileOpen(FilenameMPP)
 Set wb = Workbooks.Open(FilenameExcel)

End Sub

Answer 1

You're getting the error because the documentation for the open call states that the return value for the function is a Boolean. You're attempting to assign a boolean to an object. Thus a type mismatch.

I strongly advise you to enable Option Explicit. This will help avoid some errors with your variables.

Additionally, you should consider using early binding (at least during your design and debugging phase of your development) to include the MS Project library reference.

Option Explicit

Sub project2xCell()
    Dim myMPP As MSProject.Application
    Set myMPP = CreateObject("Msproject.Application")
    myMPP.Visible = True

    Dim filenameMPP As String
    filenameMPP = OpenFileDialogMPP() 'Function to browse to a Microsoft Project file

    myMPP.FileOpenEx Name:=filenameMPP, ReadOnly:=True
    Set mpp = myMPP.ActiveProject

    '--- now you can work with your project

    myMPP.Quit
End Sub