VBA question for TimeScaleData function

[miao jie]

Hi
Now I'm try to export weekly resourece work hours to Excel file. firstly, I need write the column head for each of work week. my Code as follow

Dim TSV As TimeScaleValues, HowMany As Lon
Dim first As Date, last As Dat
first = ActiveProject.ProjectStar
last = ActiveProject.ProjectFinis
Set TSV = ActiveCell.Resource.TimeScaleData(first, last, Type:=pjResourceTimescaledActualWork, TimescaleUnit:=pjTimescaleWeeks
'Set TSV =Application.ActiveCell.Resource.TimeScaleData(frist, last, pjResourceTimescaledWork, pjTimescaleWeeks
For HowMany = 1 To TSV.Coun
rgt
xlCol = TSV(HowMany).StartDate & " - " & TSV(HowMany).EndDate -
Next HowMan

Firstly, the code is running good, but several time later
when VBA excute to Set TSV = ....... , the system tell me a error message as follow
Run-time error '1004
Application-definded or object definded erro

I'm really confuse it, anyone can help me out, thanks very much

 

[JackD]

Hi,
Now I'm try to export weekly resourece work hours to Excel file.

firstly, I need write the column head for each of work week. my Code as
follow:

Dim TSV As TimeScaleValues, HowMany As Long
Dim first As Date, last As Date
first = ActiveProject.ProjectStart
last = ActiveProject.ProjectFinish
Set TSV = ActiveCell.Resource.TimeScaleData(first, last,

Type:=pjResourceTimescaledActualWork, TimescaleUnit:=pjTimescaleWeeks)

'Set TSV =Application.ActiveCell.Resource.TimeScaleData(frist, last,

pjResourceTimescaledWork, pjTimescaleWeeks)

For HowMany = 1 To TSV.Count
rgt 1
xlCol = TSV(HowMany).StartDate & " - " & TSV(HowMany).EndDate - 1
Next HowMany

Firstly, the code is running good, but several time later,
when VBA excute to Set TSV = ....... , the system tell me a error message as follow:
Run-time error '1004'
Application-definded or object definded error

I'm really confuse it, anyone can help me out, thanks very much.

There might be a problem with what your "ActiveCell" refers to.
I would avoid using active cell.
Instead I would do something like

Dim r as Resource
for each r in ActiveProject.Resources
r.timescaledata ...
'other code here
next r

Another problem is that you might be over-running the TSV.Count

I am confused why you are using a TSV from a single resource and using it as
the header.
Are you just trying to export data for one resource at a time?

-Jack

 

[Rod Gill]

Hi,

If the active cell is in a resource sheet, then that code should work.
Otherwise try:

Dim Tsvs as TimescaleValues
Dim tsv as TimescaleValue
Set Tsvs=Activeproject.Resources("Resource Name").TimeScaleData(first,
last, Type:=pjResourceTimescaledActualWork, TimescaleUnit:=pjTimescaleWeeks)
for each Tsv in Tsvs
'Excel code
Next tsv

--
For VBA posts, please use the public.project.developer group.
For any version of Project use public.project
For any version of Project Server use public. project.server

Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.projectlearning.com/

Hi,
Now I'm try to export weekly resourece work hours to Excel file.

firstly, I need write the column head for each of work week. my Code as
follow:

Dim TSV As TimeScaleValues, HowMany As Long
Dim first As Date, last As Date
first = ActiveProject.ProjectStart
last = ActiveProject.ProjectFinish
Set TSV = ActiveCell.Resource.TimeScaleData(first, last,

Type:=pjResourceTimescaledActualWork, TimescaleUnit:=pjTimescaleWeeks)

'Set TSV =Application.ActiveCell.Resource.TimeScaleData(frist, last,

pjResourceTimescaledWork, pjTimescaleWeeks)

 

[miao jie]

thanks, it's worked when I used activeProject.resources(1).timescaledat

and if I use activeCell.resources.timescaledata, it can not work. for curious, what's the different usage between ActiveProject and activeCell?? thanks again

 

[JackD]

Active project is the whole project.
As long as there is one resource in the project then activeproject.
resources(1) will always work.
Activecell refers to whichever cell is active. There may be no cell selected
and if there is not a currently selected cell then activecell will fail.
I never use activecell. Always activeproject.tasks, activeproject.resources
etc.

-Jack

thanks, it's worked when I used activeProject.resources(1).timescaledata

and if I use activeCell.resources.timescaledata, it can not work. for

curious, what's the different usage between ActiveProject and activeCell??
thanks again