Get specific data from different MS Project Views

Question

Dear community,

I'm searching for a way to transfer specific data from MS Project into Excel.

I know the basic way but it doesn't transfer the data I'm looking for.

I want to get the data from the left and the right side of the attached view into Excel.

In German the view ist called "Vorgang: Einsatz"

All attempts so far transfered only the data of the left side

Answer 1

Alex, I do this every day with copy/paste. It is a lot easier to copy the data from the right hand side grid if you first reduce the timescale resolution right down to very thin, and it can still be copied even if the columns are so thin that the cells display as ####.

Of course, once the data is in excel you need to run a replace to get rid of the "h".

Answer 2

Alex Geldmacher,

Indeed VBA is the way to go but the code you need is more involved than the simplistic code posted by Narendra. I have several VBA macros that export Project data to Excel and some of the macros export time scaled data (right side of Task Usage view). From your initial screen shot it looks like you want daily man hours as shown on the Task Usage view. Is that all you need or do you also want other time scaled data?

If you are interested, you can contact me at the below address. I will ask some questions.

John

jmacprojataticlouddotdotcom

(remove obvious redundancies)

Answer 3

Dear Naren,

thank you for your super fast and detailed reply.

I tried the steps 1. to 5. that you describe above already.

But whatever I try, only the data on the left side is transfered, means content from the columns like "Task Name", "Start Date", "Finish Date", "Resource Names", "Work", etc.

The data from the right side, means the specific allocation of hours per day to the line item on the left side and with that to the task and the ressource is, what I need to create a forcast for the next week (who will do what and how many hours will they spent).

As these columns on the right side don't have the names/headlines that can be chosen in the export wizard I can't select them during the process.

The columns I need have sort of unspecific headlines naming the day the the work will happen, means "M,D,D,D,F" so I don'T know how to tell the wizard to export this data.

I don't have much expertise in VBA but from looking at the skript you added, it seems that the skript is also adressing the columns on the left and will not cover the columns on the right.

Anyhow, thank you very much for your support, I will try the way via VBA, if I find someone with the specific expertise.

Answer 4

Dear Alex,

Good day! Thank you for your detailed information. We are here to assist you.

It sounds like you're trying to transfer data from the "Vorgang: Einsatz" view in MS Project to Excel, but you're only getting the data from the left side. I am not sure if you followed below steps:

May I know if you follow below steps to transfer both the left and right-side data?

1. Open your project in MS Project.
2. Go to the "View" tab and select the "Vorgang: Einsatz" view.
3. Click on "File" > "Save As" and choose "Excel Workbook" as the file type.
4. In the "Export Wizard," select "Selected Data" and choose the fields you want to export from both the left and right sides of the view.
5. Follow the prompts to complete the export process.

If the above method is not working, there is two another options Power Automate and VBA coding. However, both are beyond of our expertise, Since our community is mainly focus on the SharePoint online general and build-in features issues and this specific query might require more specialized assistance, I recommend visiting the Power Apps Community Forum for Power Automate and Stack Overflow - Where Developers Learn, Share, & Build Careers for VBA Script. The experts and other users there have a wealth of knowledge and should be able to provide the detailed help you need. Both the communities are great resource for specialized assistance. And Stack Overflow is Microsoft's recommended platform for VBA code queries.

Meanwhile, as you know this beyond of our expertise, but I tried to do my research on internet I found below script: (Please consult with VBA expert)

Using VBA to Export Data:

If the built-in export options don't meet your needs, you can use VBA (Visual Basic for Applications) to create a custom export script.

Open MS Project and press Alt + F11 to open the VBA editor and insert a new module and paste the following code:

Sub ExportVorgangEinsatzToExcel()

    Dim proj As Project

    Dim task As Task

    Dim xlApp As Object

    Dim xlBook As Object

    Dim xlSheet As Object

    Dim row As Integer

    Set proj = ActiveProject

    Set xlApp = CreateObject("Excel.Application")

    Set xlBook = xlApp.Workbooks.Add

    Set xlSheet = xlBook.Sheets(1)

    ' Add headers

    xlSheet.Cells(1, 1).Value = "Task Name"

    xlSheet.Cells(1, 2).Value = "Start Date"

    xlSheet.Cells(1, 3).Value = "Finish Date"

    xlSheet.Cells(1, 4).Value = "Resource Names"

    xlSheet.Cells(1, 5).Value = "Work"

    xlSheet.Cells(1, 6).Value = "Actual Work"

    row = 2

    For Each task In proj.Tasks

        If Not task Is Nothing Then

            xlSheet.Cells(row, 1).Value = task.Name

            xlSheet.Cells(row, 2).Value = task.Start

            xlSheet.Cells(row, 3).Value = task.Finish

            xlSheet.Cells(row, 4).Value = task.ResourceNames

            xlSheet.Cells(row, 5).Value = task.Work

            xlSheet.Cells(row, 6).Value = task.ActualWork

            row = row + 1

        End If

    Next task

    xlApp.Visible = True

End Sub

Run the script by pressing F5. This will export the task data, including resource assignments, to a new Excel workbook.

---

Another option, you can also use Power Automate to create a flow that transfers data from MS Project to Excel. Create a new flow with a trigger that suits your needs (e.g., when a new task is created or updated). Add actions to get the task details from MS Project and then add rows to an Excel table.

Please accept my apologies for redirecting you to the related team support, as the moderators in this community have limited resources and permission and to get the quick and better assistance, we requested for this.

I hope you are staying safe and well! Have a great day and take care! 😊 

Sincerely, 

Naren | Microsoft Community Moderator