none
Import MS Project (.mpp) files into Excel RRS feed

  • Question

  • Greetings!

    I'm trying to import MS Project files into an Excel file via VBA. It proves to be a difficult Enterprise.

    Here's a part of the VBA:

    For i = 1 To count
        Set ws = Sheets(Sheets("1").Index + i - 1)
    
        MyFile = Dir(sPad & "*.MPP")
        
        'On Error Resume Next
        Set PrjApp = CreateObject("MSProject.Application")
            'Set PrjApp = New MSProject.Application
        'End If
        'On Error GoTo 0
        PrjApp.ScreenUpdating = False
        PrjApp.Visible = True
    
        'Open MS Project file
        PrjApp.Application.FileOpenEx sPad & MyFile
        Set aProg = PrjApp.ActiveProject
        OutlineShowAllTasks
     
        'Copy the project columns and paste into Excel
        For k = 1 To cols
            SelectTaskColumn Column:=Arr(k)
            EditCopy
            Set rng = ws.Cells(2, col + k - 1)
            rng.PasteSpecial xlPasteValues
            'rng.PasteSpecial xlPasteFormats
        Next k
    
    Next i
    

    The macro is unstable: it seems that only the first time the macro is executed, it works. Afterwards it will give an error code, informing me that the external server(s) or connections cannot be found. Even so: if executed for the first time, the column 'Project (name)' will not be imported. Other columns, no problem. I expect that, if the column would get to 'Resources', this would also be problematic to import.

    Perhaps I ought to post this problem on the MS Project forum as well.
    Thanks for any help!

    Maarten

    Monday, August 20, 2018 9:00 AM

All replies

  • Follow up on the question above:

    * The macro opens the project file consistently at this point, presumably is because of the 'CreateObject' statement? Earlier I worked with 'Getobject' statement.

    * The column 'Project name' (in English: 'Task name'), has a different name than the header would have it: its name is simply 'Name'. By recording a macro I have found this. Also this is the case for duration: its column header is 'Doorlooptijd', while the actual name is 'Duur'.

    Just glad that I was able to work it out, but it would be good if the developer's team have a look at this, in my opinion, inconsistency.

    Regards,

    Maarten

    Monday, August 20, 2018 10:13 AM