none
Export Data from MSP to Excel using VBA

    Question

  • Hi,

    Want to Export MPP data to Excel Using VBA. 

    Note:  Want to create a New Excel File Every time.


    Thanks, Parth

    Monday, August 26, 2013 6:28 AM

All replies

  • Parth,

    Okay, so what's the question?

    For reference, I use the following code sequence:

    On Error Resume Next
    Set Xl = GetObject(, "Excel.application")
    If Err <> 0 Then
        On Error GoTo 0
        Set Xl = CreateObject("Excel.Application")
        If Err <> 0 Then
            MsgBox "Excel application is not available on this workstation" _
                & vbCr & "Install Excel or check network connection", vbCritical, _
                "Notes Text Export - Fatal Error"
            FilterApply Name:="all tasks"
            Set Xl = Nothing
            On Error GoTo 0     'clear error function
            Exit Sub
        End If
    End If
    On Error GoTo 0
    Xl.Workbooks.Add

    John

    Monday, August 26, 2013 3:13 PM
  • Thanks, 

    But Please show me how to bring data from MSP to Excel Also.


    Thanks, Parth

    Tuesday, August 27, 2013 6:31 AM
  • Parth,

    There are several ways to do that. I often set up a series of arrays and fill those with the desired Project data. Then I use the aforementioned code to open Excel and set up the worksheet formatting. Finally, I dump the array data into a pre-formatted worksheet. Many users use a more direct approach (i.e. read a set of Project data, write it into Excel, read the next set of Project data, write it into Excel, and so forth). A third approach is to dump the Project data into a text file and then open that text file from Excel. This latter approach allows you to save the data to be exported. I have used all three approachs in various macros I written over the years.

    As far as the code to do any of the above, it depends on what data you want to export. For example, is it just static task data or is it timescaled data? Do you need to massage the data for export (e.g. convert calendar based Project data into accounting month data, graph the data in Excel, etc.)?

    If you want to learn more about Project VBA, I highly recommend Rod Gill's book on the subject. The book has several examples of sample code. For more information, see projectvbabook.com

    John

    Tuesday, August 27, 2013 3:48 PM