Problem - Exporting data from Project to Excel

[Guest]

Hi all togehter!

I want to export both TimeScaleDatas and information out of the
resources-Area from MS Project to Excel. Here I have some problems
realizing it.

i) First I want to set the TimeScaleUnit to a pre-defined value, e.g.
Months. I have found no expression which finally takes effect by
changing the interval on the right side. (This should also works as
stand-alone tool)

ii) Further I want to define which information is displayed as
TimeScaleData on the right side. Here I just want select e.g. the
"Cost"-Row. How do I change it if e.g. also "Work" etc. is active?

iii) If I copy a value out of the left sides' .Work-Resource-Area, the
comma (",") is not be pasted in the new document, so that for example
2,33 [Hours] moves to 233 [Hours]. Why?

iiii) Finally, after all settings are done I want to copy special
information from the TimeScaleData. BUT: I have not found any way to
get these values in a direct way as someting like
"ActiveProject.Resources(Row).TimeScaleValues(Column)" so that I use
copy & paste as a temporary solution*. Therefor I have to jump from the
left side over to the right one. "PaneNext" has no effect!! Therefor I
have up to present to ensure that the TimeScale-Field has the focus
when I run the macro. But my intention is, that the macro itsself
checks which element is focused and changed it automatically if needed.

It would be assist me greatly if somebody could help me!

Kind Regards!
Nachtigall

*Does anybody know such an expression?

 

[Jan De Messemaeker]

Hi,

You have to generate the TimescaleValues object through the Timescaledata
function.
Look up the help on that one, it is fairly explicit.
After that you will never more worry about rows and columns and copy and
paste.
HTH

 

[Guest]

Hi!

I have tried it several times by using the TimescaleValues-Object as
suggested. But it doesn't work the right way. The returning values
weren't the required ones but numbers I couldn't find anywhere in the
active project.

Do you maybe have a demo-code where I can see i) how you get data from
the right side (TimeScaleData) and in addition ii) how to show / hide
provided rows like Cost, Work, etc.?

 

[Jan De Messemaeker]

Hi,

I'll have to give your question a thought because there is at the base a big
misunderstanding.
Why worry about inserting lines, the timescaleValue object totally ignores
the view. You can even use it with the gantt chart or a resource graph or
anything active.
So as long as you keep looking for a cell you will keep searching in vain.

Here's an example that returns the actual work for the first week of a task
(let's say TaskY)

Dim TSV as timescalevalues
dim AW as double

set tsv=TaskY.timescaledata startdate:=tasky.start, _
enddate:=tasky.finish, type:=pjtasktimescaledactualwork, _
timescaleunit:=pjtimescaleweeks

AW=tsv(1).value

As you see there is no reference whatsoever to a selected cel etc.
Two remarks:

- Work will always be expressed in minutes
- You may have conversion error when a cell is empty. Sioem code to
intercept that may be necessry.
HTH

 

[Mike Glen]

Hi Nachtigall,

Next time, try posting on the developer newsgroup as this one is trying to
close down. Please see FAQ Item: 24. Project Newsgroups. FAQs, companion
products and other useful Project information can be seen at this web
address: http://www.mvps.org/project/.

Mike Glen
Project MVP

 

[Rod Gill]

Hi,

I always use Val(tsv(1).Value)

If the value of the time slice is empty, value returns zero: no exception
code required.

 

[Guest]

Jan: Thank you for the code and telling some basics about the
TimeScaleValue-Object. I will try it and send a feedback about how it
works.

@Mike: If there should be any more questions I will post in the
project.developer-newsgroup in the future.

Best Regards,
Nachtigall

 

[Jan De Messemaeker]

Hi,

Even after writing thousands of lines of code, one can still learn new
functions.
Thanks Rod.