Forum Discussion
Extract Data with VBA in the Project
Hi guys, I need some help.I need to extract data from the project to connect to Power BI. I'm currently using the feature in the database model by going to Report>Visual Report>Save Data Select: Task SummaryField Picker (for custom fields) Save Database However, the chronogram is too large and I'm getting an error on this screen asking me to reduce/separate the project. I wanted to do this export in VBA, I even tried using the VBA code below, but I couldn't find a parameter to export the custom fields.
Sub Macro1()
VisualReportsSaveDatabase strNamePath:="C:\Users\tulio.oliveira\Downloads\Chronogram_Consolidated_V5.mdb", PjVisualReportsDataLevel:=pjLevelWeeks
End Sub
Hi Tulio.
By way of visual reports, I don't think it's possible to export custom fields, not even by saving the database, at least I never managed to do it after many attempts, even though it's shown as an eligible option.
In a quick way, you can copy and paste the task table into Excel: Select the entire table with Ctrl+Shift+Space, or click on the top left corner of the table, and then Ctrl+C to copy and paste into an Excel sheet later. Obviously the custom fields must be included in the Project task table.
Another option is to export the selected fields to Excel, including the custom ones, through the Wizard that is shown in Save As, choosing Type Excel Workbook...
You can also create a simple macro (VBA) that exports fields, including the custom fields. There are many examples on the Internet.
In any of these options, you should not have limitations on the size of the data to export, unless it is an immensely large file, which does not seem to be the case that you show, or you have limitations on your devices.
I hope this information helps you.
Ignacio
Thanks Ignacio_Martin, but this way it wouldn't help me copying (CTRL + C) and pasting into Excel. As I said before, I'm doing the extraction in a structured way by exporting to an Access database model available within Project. It already brings me all the structured tables, including some tables with granularity (days, weeks, months). However, as the schedule grows, it no longer allows exporting. I tried using VBA, but the method I used didn't export the "CubeDynamicCustomFieldTask" table, which is responsible for the custom fields.
In addition, the export of the MSP_EpmTaskByDay table already exceeds 1 million rows due to the weekly granularity.
TulioOliveira,
You can export any type of Project data to Excel (or Access) with VBA but it would have to be per task, resource and/or assignment instead of "en-masse" as a cube. I've written many macros that export all kinds of Project data to Excel.
Just curious, what Project fields are you trying to export?
John
Hi Tulio.
Considering that Access has a database size limitation of approximately 2 GB, it is very likely that you will not be able to export projects over a certain duration and with multiple tasks.
Depending on the information you want to obtain, you could look for an appropriate solution.
There are Project fields that are time-phased (byday) and many others (most) that are not, such as custom fields, which would be easily exportable.
Ignacio
Hi Tulio.
Custom fields in Ms Project are constant over time, they always have the same unique value assigned throughout the project for the task or resource, so you don't need to granulate anything.
If what you need is to export those 7 custom fields, you just need to include them in the task table columns and copy and paste into Excel, or if you prefer you can do an export to Excel via Save As with the xls file type, which are almost immediate procedures.
Only if you need to manage cost field information or resource work hours in different periods, the granular data you refer to makes sense, but not for custom fields, or any other type, other than cost or work, except for some fairly rare small exceptions.Ignacio
Exporting data from Microsoft Project to Power BI via VBA can be a bit tricky, especially when dealing with custom fields. Unfortunately, the Visual Reports Save Database method doesn’t support direct inclusion of custom fields
Hi, custom fields are the easiest to extract via VBA from a Project file to CSV or Excel files, for example, which are the ones that Power BI can manage, among other common procedures. As I mentioned before, for each task or resource, you only have to extract one value from each custom field, not like for example with timephased fields, which are those shown in the right table of the Task Usage and Resource Usage view. For these fields, such as Cost or Work, you need to import the values of each field by task, or resource, for each day that the task or assignment lasts, which considerably increases the size of the imported data.
Regards
Ignacio
