Export to Excel as text not numeric

[Chuck Gould]

Project Server 2003 SP2 and Project Server 2007 SP1

One of the irritating features of the Export to Excel option is that by
default when data is exported from either Project Server 2003 or Project
Server 2007 it appears as text with the numerics typically having a trailing
"h" for hours or "d" for days.

Obviously this can be handled in Excel with a macro, but I don't understand
why it is left to the user to convert data to a usable format.

Does anyone know how to make the Export to Excel export data as numeric and
date data without the appended suffixes.

 

[Rod Gill]

Hi,

That isn't configurable I'm afraid. However, in Excel you can do a search
and replace to convert to numeric format:

Select all cells with hourly data
Ctrl+h for Replace (or Edit, Replace)
Search for h
Replace with (delete contents of replace box or leave empty
Replace all
OK

The h after all numbers is deleted and Excel converts automatically to
numeric format so you can Sum etc.


--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Chuck Gould]

Rod,

Thank you for your reply. It at least tells me I am not missing something
somewhere.

By the way I am a fan. I have your book on VBA programing in Project and
consider it a valuable resource. Excellent resource.

 

[Rod Gill]

Love it when people say that!! )

Many thanks. Actually I think there was someone in the back row who might
not have heard, if you could just.....?!

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Gary L. Chefetz [MVP]]

Chuck:

Well, then you should have no trouble cobbling together a macro that does
the spreadsheet cleanup for you!

--

Gary L. Chefetz, MVP
MSProjectExperts
For Project Server Consulting: http://www.msprojectexperts.com
For Project Server FAQS: http://www.projectserverexperts.com

 

[Chuck Gould]

Gary,

I coded a macro that amoung other things did the spreadsheet cleanup some
time ago and have distributed it to my clients.

I guess the frustrating thing is why do we have do do this. You would think
that Microsoft would have a more robust solution for exporting to their own
tools.

This is illustrated in my other post of "Export Manageusers to Excel" which
documents the problems I have expereinced associated with Project Server 2007
and exporting to Excel.

You would think that Microsoft would be better integrated with itself.

 

[Gary L. Chefetz [MVP]]

Chuck:

Many folks use this feature for reporting data, not massaging it, so who is
to say what "the correct format is." It is not correct for you, but for many
others this works just fine. I think it would be more difficult to do the
conversion the other way around for those who want reporting only. It would
be very nice if the system gave you the option. I added this to the wish
list.

--

Gary L. Chefetz, MVP
MSProjectExperts
For Project Server Consulting: http://www.msprojectexperts.com
For Project Server FAQS: http://www.projectserverexperts.com

 

[Chuck Gould]

Gary,

If they added the option for format the data that would be fantastic.

If you are adding things to the wish list, please consider the issue I
posted in "Export Manageusers to Excel". It would be nice if the Export to
Excel feature in 2007 worked as well for all Export to Excel options as is
does in Project Cetner and Resource Center.

The workaround I found about "Do not save encrypted pages to disk" seems to
intefere with Microsoft Live Meeting.

Thanks for the feedback and support Gary!

 

[Gary L. Chefetz [MVP]]

Chuck:

The Project Center and Resource Center use the old Active X object, that's
why they work so much better. Let's hope that the new .Net grid objects can
be beefed up to the old Active X standard.

--

Gary L. Chefetz, MVP
MSProjectExperts
For Project Server Consulting: http://www.msprojectexperts.com
For Project Server FAQS: http://www.projectserverexperts.com

 

[Chuck Gould]

Gary,

Thank you for explaining why the experience varys. It helps to confirm that
there is a difference in implementation. I was wondering if it was something
I had done (or not done) on my side in work station configuration or
configuration of Project Server 2007.

This is why I think a work station configuration chapter would be so
beneficial in your books. I suspect I am not the only one who has been
banging their head agaisnt the wall trying to figure these anomalies out.

I know you have to make trade-offs in the books, perhaps you could offer an
Advanced book that would contain material such as detailed workstation
configuration, portfolio analyzer access, and reporting recipes.

I know I would buy it. If you need a beta reviewer let me know.

Thanks again for all your support, the classes, and the participation in the
forum.

 

[Gary L. Chefetz [MVP]]

Chuck:

We're exploring ways to include more content without jeopardizing the
format. Thanks for the words of encouragement.

--

Gary L. Chefetz, MVP
MSProjectExperts
For Project Server Consulting: http://www.msprojectexperts.com
For Project Server FAQS: http://www.projectserverexperts.com