Exporting from Project 2007 to Word 2003 or Excel 2003

[khindaria]

Is there a way to export a view (table comprising of rows and columns with
data only, without the Gantt Chart bars) from Project 2007 to Word/Excel 2003
in a tabular form and retaining the text formatting?

Need the table exported into Word/Excel editable so am not looking for a GIF
or TIF image. I have never used VBA scripts so am looking for non-VBA
solutions.

I do have Adobe Acrobat and tried creating PDF and then using a PDF
converter to create a Word document but most PDF converters do not retain the
columns properly and some lose text formatting also.

Thanks,
Khindaria

 

[Gérard Ducouret]

Hello Khindaria,

Exporting data into Excel is easy:
File / Save As... / Save as type : Microsoft Excel Workbook
Click Save
Then follow the wizard to buils to map Project's fields to Excel columns
To export data into Word, you have to write a VBA macro...
Hope this helps

Gérard Ducouret

 

[khindaria]

Hi Gérard,

Thanks for replying. I tried that but I lose text formatting.

The Project document shows Summary Tasks in bold and Subtasks indented. When
I save Project document and use Export Wizard, it creates an Excel sheet with
all tasks unformatted. I have to manually make all Summary Tasks bold and
indent all Subtasks. With more than 100 tasks and needing to do this 2-3
times every week, I would use a lot of time working the Excel sheet.

Is there any way I can keep the bold Summary Tasks bold in the Excel sheet.
I can figure a way to indent the Subtaasks by using macros or something.

Thanks,
Sanjeev

 

[JulieS]

Hello khindaria,

Although you said in your initial post that you weren't looking for
a VBA answer, fellow MVP Jack Dahlgren has posted a sample macro
(VBA) to accomplish part of what you seek. You can find the macro
and additional information at Jack's site:

http://masamiki.com/project/macros.htm

Look for the macro "Export Hierarchy to Excel"

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

 

[khindaria]

Hi Julie,

Thanks for your tip. I was able to copy-paste the script into a module in
VBA editor and run the macro. The resulting Excel sheet shows the Summary
Tasks in bold and puts the Subtasks (not in bold, as required) in next column
and up to this it is wonderful. There are 2 issues I am running into -

a) The macro adds a row between each task. I cannot sort in Excel or else I
will lose the connectivity between Summary Tasks and their respective Subtasks
b) I am not getting the Start and Finish Date columns by running this macro

I have step away from the computer and will be able to work on it on Monday
only. If you have any suggestions, would appreciate it greatly. If not, I
guess I will use the Export Wizard to extract the date columns and add the
columns.

Thanks a ton. A lot of what I need has been accomplished.
Khindaria

 

[JulieS]

Hello khindaria,

I'm glad Jack's macro was able to get you part way. To your
comments.

a) I believe the "blank row" you are seeing inserted is designed for
the assignment information on the task. If you have no resources
assigned, the row will be blank. You should be able to comment out
those lines or delete them. -- I've removed the lines in the
modified code below

b) I've modified Jack's code to remove the assignments information
and capture the task start and finish. I've copied the code below.
If you copy and paste be careful of any extraneous characters.

Obviously, test on a copy of your file.
==============================================
'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 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 = "Start Date"
rgt 1
xlCol = "Finish Date"

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
Set xlCol = xlRow.Offset(0, Columns)
xlCol = t.Start ' capture task start
rgt 1
xlCol = t.Finish ' capture task finish

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

==================================

I hope this helps.

Julie

 

[khindaria]

Hi Julie,

Thank you so very much. I really appreciate your help. I was able to run the
macro and export an Excel worksheet with no blank rows - exactly what I
needed.

With the previous script, I was able to get the Resource Names but if a task
was assigned to more than 1 person, each Resource Name appeared in separate
row. With the new script I do not get any Resource Names but I am all set
because I can export the Project document into Excel sheet that gives me the
Resource Names and then I can use VLOOKUP function to quickly bring the
Resource Names into the worksheet created using the macro.

You have been a big help to me and knowing what can be accomplished using
VBA, I am almost inclined to teach myself a little bit of VBA so I can tweak
an existing script to custom design it for my needs.

Once again, thanks a ton.

Best wishes,
Khindaria

 

[JulieS]

You're most welcome Khindaria. If you've decided to learn VBA for
Project, I suggest investing in Rod Gill's excellent book. See:
http://www.projectvbabook.com

Thanks for the feedback.

Julie