none
Export Microsoft Project Data Into Excel Using VBA

    Question

  • Hello,

    I am trying to export my project data to excel with live data.  I have tried using the paste as link and it worked great until I put the file on our shared drive for others to use and it started getting a #REF! error.  Now I am trying to explore a VBA option.  I need to export the following fields into excel.  The fields after % complete are custom text fields.  

    • Task Name
    • Baseline Start
    • Baseline Finish
    • Baseline 1 Start
    • Baseline 1 Finish
    • Start Finish
    • % Complete
    • # of Attempts (Number1)
    • Safety Status (Text8)
    • Notes
    • PJ ID (Text3)
    • Lead(Text4)
    • Budget (Text5)

    Friday, April 19, 2019 1:56 PM

All replies

  • swilliams9382,

    Yup, paste links will lead you down the corruption path, so a VBA solution is the best choice.

    Take a look at the following Wiki article. A few modifications to the code will get you the fields you want.

    https://social.technet.microsoft.com/wiki/contents/articles/32126.ms-project-truncated-project-notes-when-viewingexporting.aspx

    Hope this helps.

    John

    Friday, April 19, 2019 2:53 PM
  • Thank you so much!  This is amazing!

    Please excuse my ignorance (I fully intend on learning VBA in my free time but this project is hot), when I try to change the fields to what I "think" they should be, I get a "Compile error:  Sub or Function not defined" message.  Is there a list of VBA IDs for each field I could reference?

            TskNam(i) = t.Name - this works
            BStart(i) = t.BaselineStart - this does not work
            BFinish(i) = t.BaselineFinish - this does not work

    Also, is there a way to export this into an existing excel workbook?

    Friday, April 19, 2019 4:46 PM
  • swilliams9382,

    The fields you cite (t.BaselineStart and t.BaselineFinish) are exactly right. My guess is you did not define the new arrays in the Dim and ReDim statements, hence the error.

    The best source for Project VBA objects, methods, properties, etc. is the Object Browser which is the last icon on the Standard VB Editor window or you can go to view > object browser. Select the MSProject library and type in your search word.

    And yes, you can export the data to an existing Excel workbook. Instead of the CreateObject function to start Excel, you use the GetObject function. The object browser gives the syntax.

    If you need further help, you can contact me directly at the address below. I will ask some questions. But just remember, your "hot" isn't my "hot".

    I should also mention that if you want to get a good reference for Project VBA, take a look at Rod Gill's book on the subject:

    http://www.projectvbabook.com

    John

    jmacprojataticlouddotdotcom

    (remove obvious redundancies)


    Friday, April 19, 2019 5:38 PM
  • Understood!  Thank you so much!

    Unfortunately, I am not able to access the address provided for some reason.

    Monday, April 22, 2019 1:33 PM
  • I am able to export everything, however, I am still having trouble exporting to an existing worksheet.  Here is what I have:

    On Error Resume Next
    Set Xl = GetObject("C:\Users\123456\Desktop\WV Site Leader Report.xlsx", "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"


    Monday, April 22, 2019 2:49 PM
  • swilliams9382,

    Did you remove the redundant characters from my encoded e-mail address?

    With regard to your code, do you get an error message, or what happens?

    John

    Monday, April 22, 2019 3:57 PM