Exporting to Excel

[tmcfarla]

I am using MS Project 2003.

Everytime I export to Excel I have to make certain formatting changes to the
file and I am wondering if it is possible for it to be done automatically.
They are:
Date format - switching from date & time to date only. (this question has
been asked by someone back in December but they received no replies)
Adjusting the page setup
Adding a header

Thanks to this excellent resource I have been able to create customized
filters for my needs. I am hoping that answers to the questions above will
also save me time. Any help would be appreciated.

 

[William Raymond [MVP]]

Hello,

If you are using the same export maps, you can record a macro in Excel that
does this formatting for you.

1) Run EXCEL and go to Tools->Macros->Security. Set to Medium. Once
complete, exit Excel (you have to do this for the macro security level to
take affect.
2) Run MS Project and perform a save option, choosing an Excel workbook.
Make sure you select a custom or pre-existing map. Change the "Destination
Worksheet Name" to "My Project" (or whatever the name of the project is that
you are exporting).
3) Run Excel and open your exported file.
a) Go to View->Toolbars->Visual Basic.
b) Click the Record Macro option and name it "FormatMSPOutput". Also,
choose "Personal Macro Workbook" (this way you can run the macro in any new
Excel file you output from MS Project).
c) Change columns and set the formatting as needed.
d) Create a custom page header that displays the worksheet tab name
(remember, we gave it the "My Project" name, so the header of the document
will print with the project name. Set the left, middle or right to display
the worksheet's tab name, like this: "Project Name: &[Tab]" (omit the
quotes).
e) Adjust any other page setup data as needed.
4) In the Visual Basic toolbox, click the "Stop Macro" option.

Next time you do the export, open it in Excel and click the "Run Macro"
button. You can also use the Visual Basic editor to make any modifications.

Hope this helps,

-Bill

 

[tmcfarla]

Raymond,

Thank you for your detailed instructions. I am very pleased with my new
"shortcut".