none
TableEditEx help needed RRS feed

  • Question

  • Hello.

    I'm trying to change a table view or create a new table with a specific view, but I'm running into errors. The base view is the Resource Usage table. I need to add the Task Summary Name field, the Start Field, and the Finish field, then apply a Group and change the timeline view to months. I recorded a macro of my actions and this was the result:

    Sub Macro1()' Macro Macro1
    ' Macro Recorded 11/14/19 by Buck    
    
    TableEditEx Name:="Test05", TaskTable:=True, Create:=True, OverwriteExisting:=True, FieldName:="Task Summary Name", Title:="", Width:=10, Align:=0, ShowInMenu:=False, LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1, HeaderAutoRowHeightAdjustment:=False, WrapText:=False    
    TableEditEx Name:="Test05", TaskTable:=True, NewFieldName:="Name", Title:="", Width:=10, Align:=0, LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1, HeaderAutoRowHeightAdjustment:=False, WrapText:=False    
    TableEditEx Name:="Test05", TaskTable:=True, NewFieldName:="Start", Title:="", Width:=10, Align:=0, LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1, HeaderAutoRowHeightAdjustment:=False, WrapText:=False    
    TableEditEx Name:="Test05", TaskTable:=True, NewFieldName:="Finish", Title:="", Width:=10, Align:=0, LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, AlignTitle:=1, HeaderAutoRowHeightAdjustment:=False, WrapText:=False, ShowAddNewColumn:=False    
    TableApply Name:="Test05"    
    TimescaleEdit MajorUnits:=1, MinorUnits:=2, MajorCount:=1, MinorCount:=1, TierCount:=2    
    GroupApply Name:="ExportNameAndProject"
    End Sub

    When I try to change up the table names and run the macro, I receive the following error:

    Run-time error '1101': The view or report cannot be displayed because the table "Test06" on which it is based has been deleted.

    I'm testing this on an empty project so there aren't any custom data fields or views. I've also tested it against a real project with the same results.

    So, can anyone tell me what is going on here?

    Thanks,

    Buck

    Thursday, November 14, 2019 8:17 PM

Answers

  • Buck,

    Yes, the Project object model is a little different but if you already have a working knowledge of VBA, the object browser for Project is very helpful even though it does have a few errors.

    The following macro was written for another user about a year ago. You should be able to adapt it to fit your needs. Just be aware that you need to set a reference to the Excel Object Library before running or you will immediately hit a run time error. Once the reference is set, you're good to go.

    If this answers your question, please mark my response as the answer.

    John

    'Exports Resource information and weekly work, baseline work and actual work
    'Author: John-Project
    'Initial release: 10/13/18 9:00 am
    Option Explicit
    Public Const ver = " 1.0"
    Public Xl As Excel.Application
    Public WS1 As Worksheet, WS2 As Worksheet
    Public xlRange As Range
    Public TotWks As Integer
    Public PrSt As Date, PrFi As Date, Dat As Date
    Public i As Integer, j As Integer, p1 As Integer, Delta As Integer
    Public k As Long, TimSt As Long, TotTim As Long
    Sub ExportTimescaleData()

    Dim r As Resource
    Dim a As Assignment
    Dim ResSt As Date, ResFin As Date
    Dim TSV1 As TimeScaleValues, TSV2 As TimeScaleValues

    'opening user interface
    MsgBox "This macro exports Resource Information and" & vbCr _
        & "Resource weekly timescaled data for:" & vbCr _
        & "   Work" & vbCr & "   Baseline Work" & vbCr & "   Actual Work" & vbCr _
        & vbCr & "to a new Excel Workbook. When complete the user will be shown an Excel Save As prompt", _
        vbInformation, "Timescale Export - ver" & ver

    'find start and finish of plan to establish index reference for weekly values
    PrSt = ActiveProject.ProjectStart
    PrFi = ActiveProject.ProjectFinish
    TotWks = DateDiff("ww", PrSt, PrFi)

    'set up an new instance of Excel, or if Excel is not running, start it
    On Error Resume Next
    Set Xl = GetObject(, "Excel.application")
    If Err <> 0 Then
        On Error GoTo 0
        Set Xl = CreateObject("Excel.Application")
        If Err <> 0 Then
            MsgBox "Excel application is not available on this workstation" _
                & vbCr & "Install Excel or check network connection", vbCritical, _
                "Project Data Export - Fatal Error"
            FilterApply Name:="all tasks"
            Set Xl = Nothing
            On Error GoTo 0     'clear error function
            Exit Sub
        End If
    End If
    On Error GoTo 0
    'create a workbook with two worksheets
    Xl.Workbooks.Add
    Xl.ActiveWorkbook.Worksheets(1).Name = "Resource Information"
    Set WS1 = Xl.ActiveWorkbook.Worksheets(1)
    Xl.ActiveWorkbook.Worksheets(2).Name = "Weekly Data"
    Set WS2 = Xl.ActiveWorkbook.Worksheets(2)

    'Keep Excel in the background and minimized until spreadsheet is done (speeds transfer)
    Xl.Visible = False
    Xl.ScreenUpdating = True
    Xl.DisplayAlerts = False
    TimSt = Timer 'capture start time of export
    'pre-format worksheets
    ShFormat1
    ShFormat2

    'First, populate resource information worksheet
    Set xlRange = WS1.Range("A2")
    i = 0
    For Each r In ActiveProject.Resources
        If Not r Is Nothing Then
            xlRange.offset(i, 0).Value = r.Text3  'Resource Type
            xlRange.offset(i, 1).Value = r.Name
            xlRange.offset(i, 2).Value = r.Text5  'Role
            xlRange.offset(i, 3).Value = r.Text4  'Department
            xlRange.offset(i, 4).Value = r.Text1  'Title
            xlRange.offset(i, 5).Value = r.Text2  'Tracked
            i = i + 1
        End If
    Next r

    'Second, populate weekly data worksheet
    Set xlRange = WS2.Range("B2")
    'initialize worksheet row counter
    i = 0
    For Each r In ActiveProject.Resources
        xlRange.offset(i, -1) = r.Name
        If r.Assignments.count > 0 Then
            'resource start and finish fields are not directly readable with VBA
            '   so need to cycle through all assignments and find earliest and latest
            ResSt = "12/31/2049": ResFin = "1/1/1984"
            For Each a In r.Assignments
                If a.Start < ResSt Then ResSt = a.Start
                If a.Finish > ResFin Then ResFin = a.Finish
            Next a
            'determine resource start offset from project start
            Delta = DateDiff("ww", PrSt, ResSt)
            xlRange.offset(i, 0).Value = "Work"
            xlRange.offset(i + 1, 0).Value = "Baseline Work"
            xlRange.offset(i + 2, 0).Value = "Actual Work"
            'write weekly work and actual work values
            Set TSV1 = r.TimeScaleData(StartDate:=ResSt, EndDate:=ResFin, _
                Type:=pjResourceTimescaledWork, timescaleunit:=pjTimescaleWeeks)
            Set TSV2 = r.TimeScaleData(StartDate:=ResSt, EndDate:=ResFin, _
                Type:=pjResourceTimescaledActualWork, timescaleunit:=pjTimescaleWeeks)
            p1 = Delta  'set column start pointer
            For k = 1 To TSV1.count
                If IsNumeric(TSV1(k)) Then
                    xlRange.offset(i, p1).Value = Round(TSV1(k).Value / 60, 2)
                End If
                i = i + 2
                If IsNumeric(TSV2(k)) Then
                    xlRange.offset(i, p1).Value = Round(TSV2(k).Value / 60, 2)
                End If
                i = i - 2 'reset row index
                p1 = p1 + 1
            Next k
            'due to bug in Project timescale baseline work does not always roll up to
            '   resource level so need to look at assignment values and add them up
            p1 = Delta
            i = i + 1 'reset row index based on last value
            For Each a In r.Assignments
                'reset TVS1 for baseline data
                Set TSV1 = a.TimeScaleData(StartDate:=ResSt, EndDate:=ResFin, _
                    Type:=pjAssignmentTimescaledBaselineWork, timescaleunit:=pjTimescaleWeeks)
                For k = 1 To TSV1.count
                    If IsNumeric(TSV1(k)) Then
                        'write to WS2, adding values for each assignment
                        xlRange.offset(i, p1).Value = xlRange.offset(i, p1) + Round(TSV1(k).Value / 60, 2)
                    End If
                    p1 = p1 + 1
                Next k
                p1 = Delta  'reset column index for next assignment
            Next a
            'reset row and column indexes for next resource
            i = i + 2
            p1 = Delta
        Else
            'no assignments for this resource so increment to next row
            i = i + 1
        End If
    Next r

    'format completed worksheets
    Xl.Visible = True
    WS1.Columns("A:F").AutoFit
    WS1.Rows(2).Select
    Xl.ActiveWindow.FreezePanes = True
    WS1.Range("A1").Select
    WS2.Activate
    WS2.UsedRange.Columns.AutoFit
    WS2.Range("C2").Select
    Xl.ActiveWindow.FreezePanes = True
    TotTim = Timer - TimSt
    Xl.Visible = False

    MsgBox "Export is complete" & vbCr & _
        "   Export time: " & TotTim & " sec", vbInformation
    Xl.Visible = True
    Xl.GetSaveAsFilename InitialFileName:="Resource Tracking"

    Set Xl = Nothing
    End Sub

    'subroutines to pre-format worksheets
    Sub ShFormat1()
    'set up Worksheet 1 (WS1) format
    WS1.Range("A1") = "Resource Type"
    WS1.Range("B1") = "Resource Name"
    WS1.Range("C1") = "Role"
    WS1.Range("D1") = "Department"
    WS1.Range("E1") = "Title"
    WS1.Range("F1") = "Tracked"
    WS1.Rows(1).Font.Bold = True
    End Sub
    Sub ShFormat2()
    'set up Worksheet 2 (WS2) format
    WS2.Range("A1") = "Resource Name"
    WS2.Range("B1") = "Work Details"
    Set xlRange = WS2.Range("B1")
    Dat = PrSt
    'write weekly dates starting with cell B1 offset by i index
    For i = 1 To TotWks
        xlRange.offset(0, i).Value = Format(Dat, "d-mmm-yy")
        Dat = DateAdd("ww", 1, Dat)
    Next i
    WS2.Rows(1).Font.Bold = True
    End Sub

    Monday, November 18, 2019 5:09 PM

All replies

  • Just Call,

    Well, apparently you created a view table called "Test06" and that view table is no longer there (i.e. it's was deleted at some point). Or, if you created view table "Test06" for the Gantt Chart view and then tried to run your macro while in the Resource Usage view, the task view table "Test06" does not exist for resource or resource usage views.

    Try this, create a new view table called "Text06" for the appropriate view type, save it and then try your macro. Does it work now?

    John


    Thursday, November 14, 2019 9:35 PM
  • Hi John,

    Just call me Buck. Someone is already using Buck, so here we are....

    The first four lines of the macro are creating the table I need. The error appears when the TableApply command happens. I'm trying to automate a process where an existing schedule is opened, a new table is created and formatted with just the information I need to pull from the schedule, then that information is copied over to Excel where it is utilized. 

    Why does Project "delete" the table I'm trying to create with the macro?

    Buck

    Friday, November 15, 2019 3:01 PM
  • Just Call,

    Okay I noticed something else that doesn't quite gel. You apparently are trying to modify the view table for the Resource Usage view yet the recorded macro is for a task view (i.e. TaskTable=true). And the field "Task Summary Name" does not exist for task views, it only exists for the Resource Usage view.

    So, what exactly did you do and what exactly are you trying to do (i.e. your end goal, not just the process you are trying to use)? If your end goal is to export Project data to Excel, you do not need to create any special view tables to do so. I can help you get your data to Excel much more efficiently.

    John


    Friday, November 15, 2019 4:20 PM
  • John,

    Again, my name is Buck. 

    The end user was copying data from the Resource Usage view which had been modified several ways. First, the Task Summary Name, Start, and Finish columns were added. Second, the Task Summary Column was moved to position 1. Third, the Name column was set to outline level 2. 

    The user manually copies the four columns over to excel, then copies the hours from the timedata.

    I recorded a macro as I walked through the table configuration steps with the user. What you see is exactly what Project spit out. If it doesn't quite gel, you can thank Microsoft for that.

    It needs to be automated because they are dealing with over 100 schedules which are changed regularly. I'm trying to build a macro to pull the data from Project into Excel with as little user input as possible. I've figured out how to pull the data, I just need help configuring the view since they are subject to change as the project managers use them.

    Thanks,
    Buck

    Friday, November 15, 2019 4:59 PM
  • Buck,

    Sorry, I should have addressed you properly.

    Okay, what I gather from your further explanation is that you simply need to export timescaled data from Project to Excel. That being the case, recording a macro and then copying and pasting the data is definitely not very efficient and is prone to user error (e.g "oops, I missed this column or this row").

    The most efficient way to do it is to work directly with Project's objects instead of relying on the data in a particular view. The former uses background processing and and the latter uses foreground processing.

    How confident are you with VBA? If you are game, I can post a macro I wrote for another user about a year ago and you can modify it to meet your needs. It exports some basic timescaled data. If you are not quite so game, I can write a macro for your specific needs. How would you like to proceed?

    John


    Friday, November 15, 2019 5:51 PM
  • John,

    Thank you for your help! I'm pretty comfortable in VB, but most of my experience is writing for Excel, Word, and from back before Python, ArcGIS. I'm finding that the Project object model is... different. If you can post something, I can usually figure out how to tweak it for my needs.

    Again, thanks for lending a hand.

    Buck

    Monday, November 18, 2019 2:00 PM
  • Buck,

    Yes, the Project object model is a little different but if you already have a working knowledge of VBA, the object browser for Project is very helpful even though it does have a few errors.

    The following macro was written for another user about a year ago. You should be able to adapt it to fit your needs. Just be aware that you need to set a reference to the Excel Object Library before running or you will immediately hit a run time error. Once the reference is set, you're good to go.

    If this answers your question, please mark my response as the answer.

    John

    'Exports Resource information and weekly work, baseline work and actual work
    'Author: John-Project
    'Initial release: 10/13/18 9:00 am
    Option Explicit
    Public Const ver = " 1.0"
    Public Xl As Excel.Application
    Public WS1 As Worksheet, WS2 As Worksheet
    Public xlRange As Range
    Public TotWks As Integer
    Public PrSt As Date, PrFi As Date, Dat As Date
    Public i As Integer, j As Integer, p1 As Integer, Delta As Integer
    Public k As Long, TimSt As Long, TotTim As Long
    Sub ExportTimescaleData()

    Dim r As Resource
    Dim a As Assignment
    Dim ResSt As Date, ResFin As Date
    Dim TSV1 As TimeScaleValues, TSV2 As TimeScaleValues

    'opening user interface
    MsgBox "This macro exports Resource Information and" & vbCr _
        & "Resource weekly timescaled data for:" & vbCr _
        & "   Work" & vbCr & "   Baseline Work" & vbCr & "   Actual Work" & vbCr _
        & vbCr & "to a new Excel Workbook. When complete the user will be shown an Excel Save As prompt", _
        vbInformation, "Timescale Export - ver" & ver

    'find start and finish of plan to establish index reference for weekly values
    PrSt = ActiveProject.ProjectStart
    PrFi = ActiveProject.ProjectFinish
    TotWks = DateDiff("ww", PrSt, PrFi)

    'set up an new instance of Excel, or if Excel is not running, start it
    On Error Resume Next
    Set Xl = GetObject(, "Excel.application")
    If Err <> 0 Then
        On Error GoTo 0
        Set Xl = CreateObject("Excel.Application")
        If Err <> 0 Then
            MsgBox "Excel application is not available on this workstation" _
                & vbCr & "Install Excel or check network connection", vbCritical, _
                "Project Data Export - Fatal Error"
            FilterApply Name:="all tasks"
            Set Xl = Nothing
            On Error GoTo 0     'clear error function
            Exit Sub
        End If
    End If
    On Error GoTo 0
    'create a workbook with two worksheets
    Xl.Workbooks.Add
    Xl.ActiveWorkbook.Worksheets(1).Name = "Resource Information"
    Set WS1 = Xl.ActiveWorkbook.Worksheets(1)
    Xl.ActiveWorkbook.Worksheets(2).Name = "Weekly Data"
    Set WS2 = Xl.ActiveWorkbook.Worksheets(2)

    'Keep Excel in the background and minimized until spreadsheet is done (speeds transfer)
    Xl.Visible = False
    Xl.ScreenUpdating = True
    Xl.DisplayAlerts = False
    TimSt = Timer 'capture start time of export
    'pre-format worksheets
    ShFormat1
    ShFormat2

    'First, populate resource information worksheet
    Set xlRange = WS1.Range("A2")
    i = 0
    For Each r In ActiveProject.Resources
        If Not r Is Nothing Then
            xlRange.offset(i, 0).Value = r.Text3  'Resource Type
            xlRange.offset(i, 1).Value = r.Name
            xlRange.offset(i, 2).Value = r.Text5  'Role
            xlRange.offset(i, 3).Value = r.Text4  'Department
            xlRange.offset(i, 4).Value = r.Text1  'Title
            xlRange.offset(i, 5).Value = r.Text2  'Tracked
            i = i + 1
        End If
    Next r

    'Second, populate weekly data worksheet
    Set xlRange = WS2.Range("B2")
    'initialize worksheet row counter
    i = 0
    For Each r In ActiveProject.Resources
        xlRange.offset(i, -1) = r.Name
        If r.Assignments.count > 0 Then
            'resource start and finish fields are not directly readable with VBA
            '   so need to cycle through all assignments and find earliest and latest
            ResSt = "12/31/2049": ResFin = "1/1/1984"
            For Each a In r.Assignments
                If a.Start < ResSt Then ResSt = a.Start
                If a.Finish > ResFin Then ResFin = a.Finish
            Next a
            'determine resource start offset from project start
            Delta = DateDiff("ww", PrSt, ResSt)
            xlRange.offset(i, 0).Value = "Work"
            xlRange.offset(i + 1, 0).Value = "Baseline Work"
            xlRange.offset(i + 2, 0).Value = "Actual Work"
            'write weekly work and actual work values
            Set TSV1 = r.TimeScaleData(StartDate:=ResSt, EndDate:=ResFin, _
                Type:=pjResourceTimescaledWork, timescaleunit:=pjTimescaleWeeks)
            Set TSV2 = r.TimeScaleData(StartDate:=ResSt, EndDate:=ResFin, _
                Type:=pjResourceTimescaledActualWork, timescaleunit:=pjTimescaleWeeks)
            p1 = Delta  'set column start pointer
            For k = 1 To TSV1.count
                If IsNumeric(TSV1(k)) Then
                    xlRange.offset(i, p1).Value = Round(TSV1(k).Value / 60, 2)
                End If
                i = i + 2
                If IsNumeric(TSV2(k)) Then
                    xlRange.offset(i, p1).Value = Round(TSV2(k).Value / 60, 2)
                End If
                i = i - 2 'reset row index
                p1 = p1 + 1
            Next k
            'due to bug in Project timescale baseline work does not always roll up to
            '   resource level so need to look at assignment values and add them up
            p1 = Delta
            i = i + 1 'reset row index based on last value
            For Each a In r.Assignments
                'reset TVS1 for baseline data
                Set TSV1 = a.TimeScaleData(StartDate:=ResSt, EndDate:=ResFin, _
                    Type:=pjAssignmentTimescaledBaselineWork, timescaleunit:=pjTimescaleWeeks)
                For k = 1 To TSV1.count
                    If IsNumeric(TSV1(k)) Then
                        'write to WS2, adding values for each assignment
                        xlRange.offset(i, p1).Value = xlRange.offset(i, p1) + Round(TSV1(k).Value / 60, 2)
                    End If
                    p1 = p1 + 1
                Next k
                p1 = Delta  'reset column index for next assignment
            Next a
            'reset row and column indexes for next resource
            i = i + 2
            p1 = Delta
        Else
            'no assignments for this resource so increment to next row
            i = i + 1
        End If
    Next r

    'format completed worksheets
    Xl.Visible = True
    WS1.Columns("A:F").AutoFit
    WS1.Rows(2).Select
    Xl.ActiveWindow.FreezePanes = True
    WS1.Range("A1").Select
    WS2.Activate
    WS2.UsedRange.Columns.AutoFit
    WS2.Range("C2").Select
    Xl.ActiveWindow.FreezePanes = True
    TotTim = Timer - TimSt
    Xl.Visible = False

    MsgBox "Export is complete" & vbCr & _
        "   Export time: " & TotTim & " sec", vbInformation
    Xl.Visible = True
    Xl.GetSaveAsFilename InitialFileName:="Resource Tracking"

    Set Xl = Nothing
    End Sub

    'subroutines to pre-format worksheets
    Sub ShFormat1()
    'set up Worksheet 1 (WS1) format
    WS1.Range("A1") = "Resource Type"
    WS1.Range("B1") = "Resource Name"
    WS1.Range("C1") = "Role"
    WS1.Range("D1") = "Department"
    WS1.Range("E1") = "Title"
    WS1.Range("F1") = "Tracked"
    WS1.Rows(1).Font.Bold = True
    End Sub
    Sub ShFormat2()
    'set up Worksheet 2 (WS2) format
    WS2.Range("A1") = "Resource Name"
    WS2.Range("B1") = "Work Details"
    Set xlRange = WS2.Range("B1")
    Dat = PrSt
    'write weekly dates starting with cell B1 offset by i index
    For i = 1 To TotWks
        xlRange.offset(0, i).Value = Format(Dat, "d-mmm-yy")
        Dat = DateAdd("ww", 1, Dat)
    Next i
    WS2.Rows(1).Font.Bold = True
    End Sub

    Monday, November 18, 2019 5:09 PM
  • John,

    Thanks for the inspiration. I know I originally posted code from a macro developed in Project, but the end code will be in Excel, pulling data from Project files. I worked on it a bit this morning and got everything working processing in the foreground.

    Now that I can see the better way to grab the data directly, I can rework the solution to be more efficient. I really appreciate you posting the code. Your answer is marked.

    Thanks,

    Buck

    Monday, November 18, 2019 6:25 PM
  • Buck,

    You're welcome and thanks for the feedback.

    Most of the cross-application code I write is moving data from Project to Excel, as this is a Project based forum. However, I have also written several macros moving data from Excel to Project.

    John

    Monday, November 18, 2019 8:25 PM