Solved: Exporting Project 2007 Gnatt view to Excel

[krishnak]

Hi All,

I want to export the Task pane ( 6 columns) of the Gnatt view to a new Excel worksheet. I got the attached macro while browsing the related sites. The macro is expected to create a new Excel workbook and export the contents from Project view.
When I run the macro, I get the following error message:
User-defined type not define.

This message is for all the Excel worksheet data types starting from Excel.Range to other variables. When I check the data types in the drop down boxes, I do not find any reference to the Excel.<data type>. But this macro seems to be making rounds for quite some time and finds a lot of references at many web sites.
I installed MS Project 2007 recently and this is the first macro I am attempting to run on Project.

Is there any other add-in to be downloaded from the Microsoft web site?
Any help will be appreciated.

- Krishna

[krishnak]

Sorry for not attaching the macro. Here is the macro.

[VBA]
'This module contains macros which will export
'tasks to excel and keep the task hierarchy.
'modify as necessary to include other task information
'Copyright Jack Dahlgren, Feb 2002
Option Explicit
Dim xlRow As Excel.Range
Dim xlCol As Excel.Range
Sub TaskHierarchy()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim Proj As Project
Dim t As Task
Dim Asgn As Assignment
Dim ColumnCount As Integer
Dim Columns As Integer
Dim Tcount As Integer
Set xlApp = New Excel.Application
xlApp.Visible = True
AppActivate "Microsoft Excel"
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = ActiveProject.Name
'count columns needed
ColumnCount = 0
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
If t.OutlineLevel > ColumnCount Then
ColumnCount = t.OutlineLevel
End If
End If
Next t
'Set Range to write to first cell
Set xlRow = xlApp.ActiveCell
xlRow = "Filename: " & ActiveProject.Name
dwn 1
xlRow = "OutlineLevel"
dwn 1
'label Columns
For Columns = 1 To (ColumnCount + 1)
Set xlCol = xlRow.Offset(0, Columns - 1)
xlCol = Columns - 1
Next Columns
rgt 2
xlCol = "Resource Name"
rgt 1
xlCol = "work"
rgt 1
xlCol = "actual work"
Tcount = 0
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
dwn 1
Set xlCol = xlRow.Offset(0, t.OutlineLevel)
xlCol = t.Name
If t.Summary Then
xlCol.Font.Bold = True
End If
For Each Asgn In t.Assignments
dwn 1
Set xlCol = xlRow.Offset(0, Columns)
xlCol = Asgn.ResourceName
rgt 1
xlCol = (Asgn.Work / 480) & " Days"
rgt 1
xlCol = (Asgn.ActualWork / 480) & " Days"
Next Asgn
Tcount = Tcount + 1
End If
Next t
AppActivate "Microsoft Project"
MsgBox ("Macro Complete with " & Tcount & " Tasks Written")
End Sub
Sub dwn(i As Integer)
Set xlRow = xlRow.Offset(i, 0)
End Sub
Sub rgt(i As Integer)
Set xlCol = xlCol.Offset(0, i)
End Sub
[/VBA]

[mdmackillop]

Have you set a reference to Microsoft Excel xx.x Object Library?

[krishnak]

Sorry for the delayed response. I do not understand what you mean by the reference to the Excel Object Hirearchy.

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
...................
Set xlApp = New Excel.Application
xlApp.Visible = True
AppActivate "Microsoft Excel"
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add

The above code within the macro should take care of addressing the Excel objects.

Maybe I am missing something. Will you please explain in detail what you mean?

Thanks for the response.

[mdmackillop]

I don't have Project to test your code. In thre VBE you add references using Tools/References wich gives access to Excel objects

[krishnak]

Thanks, that did the trick!