Re: Exporting from Project 2007 to Word 2003 or Excel 2003

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

"khindaria" <khindaria@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:475F859A-791D-4E6D-A143-33DCB80EF91C@xxxxxxxxxxxxxxxx
Hi Julie,

Thank you so very much. I really appreciate your help. I was able
to run the
macro and export an Excel work*** 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 *** that
gives me the
Resource Names and then I can use VLOOKUP function to quickly
bring the
Resource Names into the work*** 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" wrote:

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 xl*** As Excel.Work***
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
xl***.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" <khindaria@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:0E901DD2-4B4C-4DF7-A27C-BECE5BD4E543@xxxxxxxxxxxxxxxx
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 *** 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" wrote:

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" <khindaria@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:3ABE41E5-DA2C-4B78-BAE8-F918621F0067@xxxxxxxxxxxxxxxx
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
*** 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
***.

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

Thanks,
Sanjeev

"Gérard Ducouret" wrote:

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" <khindaria@xxxxxxxxxxxxxxxxxxxxxxxxx> a écrit
dans
le
message de
news: E077875E-22F3-473B-85EC-95167A055F94@xxxxxxxxxxxxxxxx
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











.