Import unit price analysis (UPA) from a construction budgeting software to Microsoft Project

Javier T.

What you are showing in your latest screen shot is the assignment information for a particular task. And as I said, there are two ways to get information from Excel - one is with an import map and the other is via VBA. To use an import map for your data you will need to set up 3 separate worksheets in Excel whereas with VBA, the data can be extracted directly from the Excel table you show (i.e. does not need separate worksheets).

Or, you could copy and paste from your Excel table to the Task Information window for each task.

Just a word about managing material resources. In Project there is no quantity control of material resources. You specify the material (Name), the usage basis (e.g. per hour, per cubic meter, per gallon, etc.), and the cost per usage basis (i.e $/cubic meter). There is no max limit on material resources like there is with a work type resource. However, you will be able to see the total cost and total usage of each material via the Resource Usage view.

John

Many construction companies do the same as you do, where the quantities are worked out and their "unit rates" are used to calculate a cost, in some estimating tool or excel. Then some use MSP is just as a schedule of tasks without any attempt to assign resources or costs, and others go as far as you by including the resource assignments.

The problem with this approach is that the bill of quantities only gets you to the quantities and costs (of those quantities) but not yet a schedule.

I think this traditional process is putting the cart before the horse. If you start with the plan in MSP, you can build all of the resources and cost assignments there, and bypass the other tool and spreadsheets entirely.

I have nothing against import maps and VBA but I think you may be looking for an elaborate solution, when your "problem" can be solved more easily. It can't be that big of a project. How many lines of tasks do you have? A few thousand? Nothing.

If you are assigning resources in the task information dialog, you're doing it the slow way. There are various tricks to do it faster. Use the assign resources dialog. Use the details split screen. Use the resource names column and lots of copy/paste and fill down. Use filtering, sorting and grouping to make the data input easier.

Hi John,

Basically what I need is that this:

Become this:

without doing it manually.

We use other software to make the construction budget (that can be exported to Excell), and we want to use Microsoft Project because is a great tool to manage the resources and control the schedule.

Javier T.

Javier T.

I'm not sure what you mean in your second question. An import map will import resource data from an Excel resource based worksheet to Project's Resource Sheet and assignment data from an Excel assignment abased worksheet to a given task, as long as the respective Excel Worksheets for each are properly formatted.

With a VBA approach, data can be imported to wherever required in the Project file (i.e. VBA has much more flexibility but it requires programming).

Question. Why exactly do you want to use Project? And if you do want to use Project, why do you need Excel (i.e. why no just enter the data directly into Project)?

John

Hello John,

But would it be possible to import, in any of the two ways you mention, the data directly to the task? Not only in the resources data sheet, but also as resources of a specific created task?

Regards,

Javier T.