Project to Excel

[SimonNZ]

Hi there,

I have created a nice wee Excel book with some pivot tables after exporting data out of MSProj into Excel.

If I manually do another export out of Project, copy the new data into my Excel book and re-define the data for the pivot table, it seems to work OK.

My Question:
Is there some way I can get Excel to pull the data out of Project so I can setup a VBA button to do the whole thing with click?

Thanks,

Simon

 

[SkipVought]

Simon,

Export the data. Use a naming convention.

In Excel, Data > Import External Data > Import Data

I don't know your requirements, but if you EXPORT using the same filename, all you'd have to do on the Excel side is Data > Refresh

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[PDQBach]

This should get you started. Watch out for line wraps. Remember to include the necessary libraries in the VBA window (Tools | References)

Sub PDQmain()
Dim msPRJ As Object
Dim msXL As Object
Dim tsk as Task

Cells(1, 1).Value = "Please wait while MS Project is loaded ..."

'Load MS Project
'If msPRJ is running then use the existing instance.
'If it isn't runing then create an instance.

On Error Resume Next
If msPRJ Is Nothing Then
Set msPRJ = GetObject(, "msProject.Application")
If Err.Number <> 0 Then
'
' Project is not running. Create a new application.
'
Set msPRJ = New msProject.Application
DoEvents
Else
'Stop
Set msPRJ = Nothing
DoEvents
Set msPRJ = New msProject.Application
DoEvents
End If
End If
Cells(1, 1).Value = ""
msPRJ.Application.Visible = True

For each Tsk in msPRJ.Tasks
'
' do something
'
Next


ActiveWorkbook.SaveAs FileName:="PDQ.XLS", FileFormat:=xlNormal
Excel.ActiveWorkbook.Close
msPRJ.Application.Quit
Set msPRJ = Nothing
End Sub

 

[SimonNZ]

Thanks SkipVought and PDQBach. I've been pulled off onto another task but I'll try these two options later and let you know how I get on.

Cheers,

Simon