none
Timescaledata issue with 'count' RRS feed

  • Question

  • Hello

    I copied this code from another site on the web. However, I am getting a 'mismatch' error on the 'Set TSV' line. Not sure what to do. Please help if you can. FWIW, I do not understand why I have to write VBA code to get timephased reporting out of project.

    Sub exportCPRData()
     
     Dim r As Resource
     Dim rs As Resources
     Dim TSV As TimeScaleValues
     Dim TSVs As TimeScaleValue
     Dim i As Long, j As Long
     Dim xlRange As Excel.Range
     Dim xlCol As Excel.Range
     Dim xlRow As Excel.Range
     Dim xlApp As Excel.Application
    
    Set xlApp = New Excel.Application
     xlApp.Visible = True
     AppActivate "Excel"
     Set xlBook = xlApp.Workbooks.Add
     Set xlsheet = xlBook.Worksheets.Add
     xlsheet.Name = ActiveProject.Name
     Set xlRange = xlApp.ActiveSheet.Range("A1:ZZ1")
     
    xlRange.Value = "Resource Name"
     Set xlRange = xlRange.Offset(0, 1)
     xlRange.Value = ""
     
    Set TSV = ActiveProject.ProjectSummaryTask.TimeScaleData(TextBox1.Value, TextBox2.Value, TimescaleUnit:=ComboBox1.Value, count:=ComboBox1)
     For j = 1 To TSVs.count
     Set xlRange = xlRange.Offset(0, 1)
     xlRange.Value = TSVs.Item(j).StartDate
     Next j
     Set xlRange = xlRange.Offset(1, -j)
     'gets the resources and writes them into excel
     Set rs = ActiveProject.Resources
     For Each r In rs
     If Not r Is Nothing Then
     xlRange.Value = r.Name
     Set xlRange = xlRange.Offset(0, 1)
     If r.EnterpriseGeneric Then
     xlRange.Value = r.EnterpriseGeneric
     End If
     
     Set xlRange = xlRange.Offset(0, 1)
    Thank you!!

    Thursday, April 28, 2016 2:50 AM

Answers

  • Your timescale unit parameter needs to evaluate to pjResourceTimescaledActualCost which is 11.

    Your timescaledata in the code above is based on the Project Summary Task so it returns time data for the whole project.

    To get data for each resource you need

    for each res in activeproject.resources
        set tsv=res.timescaldata etc.
    Next Res


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    • Marked as answer by Mattcamus1 Tuesday, May 3, 2016 1:40 PM
    Monday, May 2, 2016 10:16 PM
    Moderator

All replies

  • Hi,

    you did not provide code for your forms, so I can't verify you are using correct data types in ActiveProject.ProjectSummaryTask.TimeScaleData. Please have a look at https://msdn.microsoft.com/en-us/library/office/ff868163.aspx for types. But there is at least one mistake: "count:=ComboBox1". ComboBox1 is no integer value.

    An easier way to get timephased data into Excel may be usage of Visual Reports within Project. . If you start e.g. visual report Baseline Work Report, select appropriate level of usage data. Excel will be opened and display a chart. Go to to sheet "Assignment Usage" and uses Resources and Tasks in rows, Weekly Calendar in columns and Work as values.

    Does that help?
    Regards
    Barbara


    To increase the value of this forum, please mark the replies that helped to solve your issue as answer. If you find answers to questions from other forum participants to be helpful, please mark them as helpful. Your participation will help others to find an appropriate solution faster. Thanks for your support!

    Thursday, April 28, 2016 9:31 AM
    Moderator
  • There are a few problems, the first is:

    Change:

    Dim TSV As TimeScaleValues
    Dim TSVs As TimeScaleValue

    To

    Dim TSVs As TimeScaleValues
    Dim TSV As TimeScaleValue

    I would look to do some redesign to make your code simpler. Without knowing more about what you are trying to achieve I can't advise further.

    At least the above fix will get you going again.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Friday, April 29, 2016 9:57 PM
    Moderator
  • Hi Barbara, thank you for your response.

    When I do that, it exports a pivot table that does not have the information I need, even when I attempt to create a custom export.

    I was able to fix the 'combobox1' issue as this:

    Set TSV = ActiveProject.ProjectSummaryTask.TimeScaleData(TextBox1.Value, TextBox2.Value, timescaleunit:=pjTimescaleDays)

    Monday, May 2, 2016 2:56 PM
  • Hi Rod,

    Thank you for your response.

    I am attempting to export my resource usage and actual costs by day and task to excel.

    I am able to export the resources by day without a problem now but I'm looking for Actual Costs by day but cannot seem to find the object to use when setting my variables for cost.

    What is/are the columns to call out in my variables to access cost data in the same manner I'm able to export resource data?

    Thank you!!

    Monday, May 2, 2016 3:00 PM
  • Your timescale unit parameter needs to evaluate to pjResourceTimescaledActualCost which is 11.

    Your timescaledata in the code above is based on the Project Summary Task so it returns time data for the whole project.

    To get data for each resource you need

    for each res in activeproject.resources
        set tsv=res.timescaldata etc.
    Next Res


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    • Marked as answer by Mattcamus1 Tuesday, May 3, 2016 1:40 PM
    Monday, May 2, 2016 10:16 PM
    Moderator
  • Thank you, Rod. I will try this and get back to you on the result.
    Tuesday, May 3, 2016 1:40 PM
  • Hi Rod,

    I got it all to work thank you!!

    Thursday, May 5, 2016 7:29 PM
  • Thursday, May 5, 2016 9:12 PM
    Moderator