Export to excel problem, fields are text fields

[kees van loon]

I'm working on with a consolidated projectfile and export the "resource
usage" screen to excel. I created a extra colum "project" to see the project
the resource has to work on.
I have two problems:
1: The colum work shows as hours, and i prefer to have it in days, is that
possible ?
2: The left and right part I copy and past to Excel. In excel the time is
writen as "6H" and I can't calculate on the cell because it is a text field.
is there a solution for ?

I also had tried to export to excel with "save as" but then it is not
possible to save the "project" field, only the task a resource is working is
exported on but not the project. (the subproject name) or is there still
another way ?

 

[Darrell]

kees van loon,

I do not think you can change the hours to days in the "Resource Usage"
table. For the data like "6H" you can do a "Replace" and change the "6H" to a
"6". When I did this I recorded it as a macro and did a replace for all
numbers 0-9. Now when I export something with data like the "6H" I just run
the macro and the entire table converts from text to numbers making it
possible to do calculations like converting hours to days.

Darrell

 

[kees van loon]

extra info:
I use Project 2007 prof so there is no "Export Timephased Data in
Excel..." .

 

[John]

kees van loon,

I do not think you can change the hours to days in the "Resource Usage"
table. For the data like "6H" you can do a "Replace" and change the "6H" to a
"6". When I did this I recorded it as a macro and did a replace for all
numbers 0-9. Now when I export something with data like the "6H" I just run
the macro and the entire table converts from text to numbers making it
possible to do calculations like converting hours to days.

Darrell,
Just for reference, the dimension for Work on the Resource Usage view
(or any other view) is determined by the setting under
Tools/Options/Schedule tab with the "work is entered in" selection box.
Note: this only applies to tasks entered after the setting has been
changed.

John
Project MVP

 

[vanita]

Hi

1. To show the work in days, in MSP file go to Tools > options > schedule >
work is entered in (change the option to days). This would change the work
from hrs. to days. Now if you export this column to an excel file, there also
work will be shown in days.

2. I am not able to understand this problem.

But if you export your consolidated file to excel through Save as, while
mapping the fields for resources, after 'name' col. that gives Resource
names, select 'Project' data field col., that would give the project name in
front of each resource.

I hope it helps.

Vanita

 

[kees van loon]

Hi Darrell

Thanks for the answer.
I thought that there was a way to not export the "H" so that the field in
Excel is a automatic a numeric field.

Kees

 

[John]

Hi Darrell

Thanks for the answer.
I thought that there was a way to not export the "H" so that the field in
Excel is a automatic a numeric field.

Kees

Kees,
Not really. Since I assume you want to export the timescaled data, some
type of process is needed to "ignore" the dimension on the raw data. The
work value is stored in Project's database in minutes and without the
dimension. It is converted to minutes, hours, or whatever as set by the
Tools/Options I mentioned in my response to Darrell, for display in
Project's views.

The only way to export the raw data is to use VBA (as Darrell
suggested), use the "analyze timescaled data in Excel" utility
(Pre-Project 2007), or the Visual Reports feature of Project 2007.

However, if you only want to export non-timescaled data you could use a
spare text field to hold the data (it will be in "raw" form), and then
use an export map.

Lots of ways to get there. Take your pick.

John
Project MVP

 

[Darrell]

I export to Excel a lot when working with resource data from PWA so I found
it easier to just record the macro and run it whenever data was exported from
project to Excel. Glad to see you can change the time scale in the usage view
before you export data, however, since I use the work field in the Entry
table I would have to switch it back to hours after the export.

Darrell

 

[kees van loon]

Thanks Vanita
I can switch from hours to days now but it is everywhere, that i don't like

To export to excel I copy and paste because there is no way (as I know) to
export timescaled data from the "save as" methode. Ik read in that i must use
vba to do that, but i have to find out now.

thanks
Kees