The Third in a Series of Short Notes About Using Project VBA
Project is designed primarily for calculating schedules using the Critical Path Method (CPM). However, there are often times you need to do more advanced calculations than are available natively in Project. The easiest solution is to turn to another application to do the calculations or to work with the resulting data.
An example of this is the use of Excel. It is actually quite simple to do this. The first thing to do is to set a reference to Excel. You do this by:
Opening Project.
Hit ALT+F11 to open the Visual Basic Editor.
From the Tools menu select "References".
Scroll down until you see the Microsoft Excel Object Library (or something similar).
Make sure the box next to it is checked.
Once that is complete you simply create a new instance of Excel and add a worksheet if necessary.
If xlApp Is Nothing Then
'Start new instance
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
MsgBox "Can't Find Excel, please try again.", vbCritical
End 'Stop, can't proceed without Excel
End If
Else
Set xlR = Nothing
Set xlApp = Nothing
Set xlBook = Nothing
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
MsgBox "Can't Find Excel, please try again.", vbCritical
End 'Stop, can't proceed without Excel
End If
End If
xlapp.Visible = False
Set xlBook = xlapp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = ActiveProject.Name
I use CreateObject here rather than GetObject based on Microsoft's recommendation in this article. If you use GetObject you may get this error:
Run-time error '429':
ActiveX component can't create object
Once that is done you can use any of the Excel VBA you need to manipulate data, format it or anything else. The following code is from a Monte Carlo simulation macro I wrote. You can find the complete thing here. What this code does is set the value of xlRow (actually a specific cell in Excel) to the value of the task finish. Then it shifts to the next cell down using the offset function.
For Each t In exportedTasks
xlRow = t.Finish
Set xlRow = xlRow.Offset(0, 1)
Next t
Once you have Excel running you can do just about anything you want with it. With a bit more code, the macro this was taken from could summarize the data and graph it. By using the two tools together you can do many things which would be difficult to do alone. I have a few other simple examples here. Be forewarned that they do not use the GetObject method. Sooner or later I'll revise them to reflect what I now know more about.
