Triggering Excel Macro from MS-Project

[Buster]

I have been trying to run an excel macro from a MS-Project VBA macro. I can
activate Excel and bring it to the foreground, but I cannot get the macro to
run.

Any ideqas on how to make this work? I have tried the examples for
word/access/powerpoint but none of the code works.

JEff

 

[JulieD]

Hi Buster

can you put your excel macro in the Workbook_Open prodecure and use project
to open the excel workbook and then the macro will fire "automatically".

Cheers
JulieD

 

[Bob Phillips]

Jeff,

Don't have MS-Project to check, but I assume it supports automation.

You should be able to do Application.Run as normal. Can you post your code?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Bob, here is the code I'm using. The lines not executing are at the bottom

===================================================
Sub AssignmentInfo2xlsByGroups()

Const objxlApp = "Excel.Application"

Dim xlApp As Object, xlBook As Object, xlRng As Object
Dim tsk As task, asn As Assignment, Tsv As TimeScaleValue
Dim res As Resource, Proj As Project, MM As Object


On Error Resume Next

'Assume Excel may or may not be running.
'First try to find a running copy of Excel
Set xlApp = GetObject(, objxlApp)

'If there isn't one, xlApp will still be Nothing
If xlApp Is Nothing Then
'Excel not found, start new copy
Set xlApp = CreateObject(objxlApp)

'Excel starts without being on Resource Bar
xlApp.Visible = True 'Make visible on Resource Bar
End If
AppActivate "Microsoft Excel"

'Create new Workbook. Add method returns a pointer
'to the new workbook
'==========Set xlBook = xlApp.Workbooks.Add

'Set Rng to point to A1 in first sheet
Set xlRng = xlApp.ActiveCell

'Write and format title
Set Proj = ActiveProject 'Using Proj is slightly faster
xlRng = "Monthly Work Report for " & Proj.Name
xlRng.Range("A2") = "As of " & Format(Date, "mmmm d yyyy")
xlRng.Range("A1:A2").Font.Bold = True
xlRng.Font.Size = 12

'Move xlRng below titles
Set xlRng = xlRng.Offset(3, 0)

'Create column titles and format
xlRng = "Group Name"
With xlRng.EntireRow
.Font.Bold = True
.WrapText = False
.ColumnWidth = 10
.HorizontalAlignment = xlHAlignCenter
.VerticalAlignment = xlVAlighnCenter
.AutoFit
End With
xlRng.EntireColumn.ColumnWidth = 20
Set xlRng = xlRng.Offset(0, 1)
xlRng.EntireColumn.ColumnWidth = 20
xlRng = "Resource Name"
Set xlRng = xlRng.Offset(0, 1)
xlRng.EntireColumn.ColumnWidth = 50
xlRng = "Task Name"
ColCtr = 3
Set xlRng = xlRng.Offset(0, 1)
'Print Dates
For Each Tsv In ActiveProject.ProjectSummaryTask.TimeScaleData( _
StartDate:=ActiveProject.ProjectStart, _
EndDate:=ActiveProject.ProjectFinish, _
Type:=pjAssignmentTimescaledWork, _
TimescaleUnit:=pjTimescaleMonths, _
Count:=1)
xlRng = Tsv.StartDate
Set xlRng = xlRng.Offset(0, 1)
ColCtr = ColCtr + 1
Next

Set xlRng = xlRng.Offset(1, -ColCtr)

'Print Resource Group
For Each Group In ActiveProject.ResourceGroup
xlRng = Project.Group.Name
Set xlRng = xlRng.Offset(0, 1)
'Print Resource; Name
For Each res In Proj.Resources
If Not (res Is Nothing) Then
xlRng = res.Name
Set xlRng = xlRng.Offset(0, 1)
'Print assignments
For Each tsk In ActiveProject.Tasks
If Not (tsk Is Nothing) Then
For Each asn In tsk.Assignments
If res.Name = asn.ResourceName Then
xlRng = tsk.Name
Set xlRng = xlRng.Offset(0, 1)
ColCtr = 1
For Each Tsv In
asn.TimeScaleData(StartDate:=ActiveProject.ProjectStart,
EndDate:=ActiveProject.ProjectFinish, _
Type:=pjAssignmentTimescaledWork,
TimescaleUnit:=pjTimescaleMonths, Count:=1)
xlRng = Val(Tsv.Value) / 60 / 7
Set xlRng = xlRng.Offset(0, 1)
ColCtr = ColCtr + 1
Next
Set xlRng = xlRng.Offset(1, -ColCtr)
End If
Next
End If
Next
End If
Set xlRng = xlRng.Offset(0, -1)
Next
Set xlRng = xlRng.Offset(0, -1)
Next
xlRng.Offset(-1, 0).CurrentRegion.Offset(1, 0).NumberFormat = _
"0.00\d;;"
'================ Fill in COlums =====================
AppActivate "Microsoft Excel"
xlApp.Run "Book2!SameAsAbove"
End Sub

 

[JulieD]

Hi Jeff

Jack Dahlgren has many project / excel interaction macros on his website,
maybe you could get some idea of how to achieve what you want by looking at
his code (http://masamiki.com/project/macros.htm - check out the export
heirarchy to excel i use it all the time).

Cheers
JulieD