Create Project summary based on the Primavera P6 Xer file

This page discusses how to create the project summary based on Xer file of project Primavera P6 package. It is presumed that the project Xer file has been made available and parsed to every distinct data table. How to read and parse the xer file is discussed on the page Xer (P6 text file) written to Excel.

Project summary

1. Select a project using a Userform

The projects in Primavera P6 package are listed in the Userform for your selection of one project. The selected project ID and name are stored in the worksheet of Excel add-in which is not visible or Windows registry for use in the next session.

Calendar Userform
Private Sub CommandButton1_Click()
    Dim i As Integer, ProjectID As Variant, ProjectName As Variant
    Dim wba As Workbook, wsa As Worksheet
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    Set wba = Workbooks("PRAP.xlam"): Set wsa = wba.Worksheets("xer")
    If ListBox1.ListIndex = -1 Then
        CommandButton1.Enabled = False
        Exit Sub
    End If
    i = ListBox1.ListIndex
    ProjectID = ListBox1.List(i, 0)
    ProjectName = ListBox1.List(i, 1)
    wsa.Cells(2, 1) = ProjectID
    wsa.Cells(2, 2) = ProjectName
    wba.Save
    Call SummaryProject(ProjectID, ProjectName)
    MsgBox "Project summary is generated."
End Sub

2. Create project summary of the selected project

Six (6) data tables are required. The tables are supposed to be generated through reading and parsing Xer file.:

  1. PROJECT
  2. PROJWBS
  3. TASK
  4. TASKRSRC
  5. CALENDAR
  6. PROJCOST
Eight(7) different categories are summarized
  1. Schedule
  2. Task
  3. Activity Progress
  4. Criticality
  5. Labor hours
  6. Non-labor hours
  7. Project cost

Sub SummaryProject(ProjectID, ProjectName)
'***********************************************************************
'   Data is stored in each distinct data table and read common variable.
'************************************************************************
    Dim COL_1() As Variant, UniqueTableRow() As Variant
    Dim DDate As Variant, Sdate As Variant, EDate As Variant
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .StatusBar = "Please wait..."
    End With
    
    Set wba = Workbooks("PRAP.xlam"): Set wsa = wba.Worksheets("xer")
    wsa.Cells(2, 1).Value = ProjectID
    
    On Error Resume Next
    Set wb = ActiveWorkbook
    Set wss = wb.Worksheets("P6 Summary")
    If Err <> 0 Then
        Set wss = wb.Worksheets.Add
        wss.Name = "P6 Summary"
    End If
    wss.Activate
    Dateformat = DateformatProcedure

    If iPROJECT = 0 Then
        Exit Sub
    End If
    
    wshtName = myTable(iPROJWBS)(1, 1)
    Call AddWorksheet(wshtName, myTable(iPROJWBS))
    wshtName = myTable(iPROJECT)(1, 1)
    Call AddWorksheet(wshtName, myTable(iPROJECT))
    wshtName = myTable(iTask)(1, 1)
    Call AddWorksheet(wshtName, myTable(iTask))
    wshtName = myTable(iCALENDAR)(1, 1)
    Call AddWorksheet(wshtName, myTable(iCALENDAR))
    wshtName = myTable(iTASKRSRC)(1, 1)
    Call AddWorksheet(wshtName, myTable(iTASKRSRC))
    wshtName = myTable(iPROJCOST)(1, 1)
    Call AddWorksheet(wshtName, myTable(iPROJCOST))
    
    '***Data date***********************
    strsql = "SELECT  cdate([last_recalc_date])  FROM [PROJECT$] where  cstr([proj_id]) = " & ProjectID & " "
    DDate = QueryResultArray(strsql)
    DataDate = Format(DDate(1, 1), Dateformat)
    
    '***Project Start date**************
     strSql1 = "SELECT cdate([act_start_date]) AS [start] FROM [TASK$]  WHERE ([status_code] = 'TK_Complete' AND cstr([proj_id]) = " & ProjectID & " ) " & _
     "UNION ALL " & _
     "SELECT cdate([early_start_date]) AS [start] FROM [TASK$]  " & _
     "WHERE (([status_code] = 'TK_NotStart' or [status_code] = 'TK_Active') and Not isnull([early_start_date]) AND cstr([proj_id]) = " & ProjectID & ")" & _
     "ORDER BY [start]"
     strsql = " select Min([start]) FROM (" & strSql1 & ")"
     Sdate = QueryResultArray(strsql)
     StartDate = Format(Sdate(1, 1), Dateformat)
     wsa.Cells(3, 1).Value = StartDate
    '***Project End date****************
     strSql1 = "SELECT cdate([act_end_date]) AS [finish] FROM [TASK$]  WHERE (cstr([proj_id]) = " & ProjectID & "  and [status_code] = 'TK_Complete')" & _
     "UNION ALL " & _
     "SELECT cdate([early_end_date]) AS [finish] FROM [TASK$]  " & _
     "WHERE (([status_code] = 'TK_NotStart' or [status_code] = 'TK_Active') and cstr([proj_id]) = " & ProjectID & "  and Not isnull([early_start_date]))" & _
     "ORDER BY [finish]"
     strsql = " select Max([finish]) FROM (" & strSql1 & ")"
     EDate = QueryResultArray(strsql)
     EndDate = Format(EDate(1, 1), Dateformat)
     wsa.Cells(3, 2).Value = EndDate
    '*******Overall tasks summary statistics********
     Dim lTask As Variant, lTT_WBS As Variant, lTT_LOE As Variant, lTT_Mile As Variant, lTT_FinMile As Variant
     Dim lTT_Task As Variant, lTT_Rsrc As Variant
     
     strsql = "SELECT count([task_id]) FROM [task$] where  cstr([proj_id]) = " & ProjectID & " "
     lTask = QueryResultArray(strsql)
     lTask = Format(lTask(1, 1), "#,##0")
     
     strsql = "SELECT count([task_id]) FROM [task$] WHERE (cstr([proj_id]) = " & ProjectID & "  and [task_type] = 'TT_WBS')"
     lTT_WBS = QueryResultArray(strsql)
     lTT_WBS = Format(lTT_WBS(1, 1), "#,##0")
     
     strsql = "SELECT count([task_id]) FROM [task$] WHERE (cstr([proj_id]) = " & ProjectID & "  and [task_type] = 'TT_LOE')"
     lTT_LOE = QueryResultArray(strsql)
     lTT_LOE = Format(lTT_LOE(1, 1), "#,##0")
     
     strsql = "SELECT count([task_id]) FROM [task$] WHERE (cstr([proj_id]) = " & ProjectID & "  and [task_type] = 'TT_Mile')"
     lTT_Mile = QueryResultArray(strsql)
     lTT_Mile = Format(lTT_Mile(1, 1), "#,##0")
     
     strsql = "SELECT count([task_id]) FROM [task$] WHERE (cstr([proj_id]) = " & ProjectID & "  and [task_type] = 'TT_FinMile')"
     lTT_FinMile = QueryResultArray(strsql)
     lTT_FinMile = Format(lTT_FinMile(1, 1), "#,##0")
     
     strsql = "SELECT count([task_id]) FROM [task$] WHERE (cstr([proj_id]) = " & ProjectID & "  and [task_type] = 'TT_Task')"
     lTT_Task = QueryResultArray(strsql)
     lTT_Task = Format(lTT_Task(1, 1), "#,##0")
     
     strsql = "SELECT count([task_id]) FROM [task$] WHERE (cstr([proj_id]) = " & ProjectID & "  and [task_type] = 'TT_Rsrc')"
     lTT_Rsrc = QueryResultArray(strsql)
     lTT_Rsrc = Format(lTT_Rsrc(1, 1), "#,##0")
     
'***Progress**************************************************
     Dim lTK_Complete As Variant, lTK_Active As Variant, lTK_NotStart As Variant
     
     strsql = "SELECT count([task_id]) FROM [TASK$] WHERE ([status_code] = 'TK_Complete' and cstr([proj_id]) = " & ProjectID & ")"
     lTK_Complete = QueryResultArray(strsql)
     lTK_Complete = Format(lTK_Complete(1, 1), "#,##0")
     
     strsql = "SELECT count([task_id]) FROM [TASK$] WHERE ([status_code] = 'TK_Active' and cstr([proj_id]) = " & ProjectID & ")"
     lTK_Active = QueryResultArray(strsql)
     lTK_Active = Format(lTK_Active(1, 1), "#,##0")
      
     strsql = "SELECT count([task_id]) FROM [TASK$] WHERE [status_code] = 'TK_NotStart' and cstr([proj_id]) = " & ProjectID & ""
     lTK_NotStart = QueryResultArray(strsql)
     lTK_NotStart = Format(lTK_NotStart(1, 1), "#,##0")
     
'***Float summary*****************************************
     Dim lNo_Float As Variant, lNeg_Float As Variant, lCritical_Path As Variant, lCritical_Act As Variant, lNon_critical As Variant

     strsql = "SELECT count([task_id]) FROM " & _
         "[TASK$] WHERE  isnull([total_float_hr_cnt]) and cstr([TASK$].[proj_id]) = " & ProjectID & ""
     lNo_Float = QueryResultArray(strsql)
     lNo_Float = Format(lNo_Float(1, 1), "#,##0")
     
     strsql = "SELECT count([task_id]) FROM " & _
         "[TASK$] INNER JOIN [CALENDAR$] ON [TASK$].[clndr_id] = [CALENDAR$].[clndr_id]" & _
         "WHERE not isnull([total_float_hr_cnt]) AND (cdbl(Val([total_float_hr_cnt]))/cdbl(val([day_hr_cnt])) < 0 ) " & _
         "AND cstr([TASK$].[proj_id]) = " & ProjectID & ""
     lNeg_Float = QueryResultArray(strsql)
     lNeg_Float = Format(lNeg_Float(1, 1), "#,##0")
     
     strsql = "SELECT count([task_id]) FROM " & _
         "[TASK$] INNER JOIN [CALENDAR$] ON [TASK$].[clndr_id] = [CALENDAR$].[clndr_id]" & _
         "WHERE not isnull([total_float_hr_cnt]) AND (cdbl([total_float_hr_cnt]/[day_hr_cnt]) = 0 ) and cstr([TASK$].[proj_id]) = " & ProjectID & ""
     lCritical_Path = QueryResultArray(strsql)
     lCritical_Path = Format(lCritical_Path(1, 1), "#,##0")
     
     strsql = "SELECT count([task_id]) FROM " & _
         "[TASK$] INNER JOIN [CALENDAR$] ON [TASK$].[clndr_id] = [CALENDAR$].[clndr_id]" & _
         "WHERE not isnull([total_float_hr_cnt]) AND (cdbl([total_float_hr_cnt]/[day_hr_cnt]) > 0 ) " & _
         "AND (cdbl([total_float_hr_cnt]/[day_hr_cnt]) <= 45 ) and cstr([TASK$].[proj_id]) = " & ProjectID & ""
     lCritical_Act = QueryResultArray(strsql)
     lCritical_Act = Format(lCritical_Act(1, 1), "#,##0")
     
     strsql = "SELECT count([task_id]) FROM " & _
         "[TASK$] INNER JOIN [CALENDAR$] ON [TASK$].[clndr_id] = [CALENDAR$].[clndr_id]" & _
         "WHERE not isnull([total_float_hr_cnt]) AND (cdbl([total_float_hr_cnt]/[day_hr_cnt]) > 45 ) and cstr([TASK$].[proj_id]) = " & ProjectID & " "
     lNon_critical = QueryResultArray(strsql)
     lNon_critical = Format(lNon_critical(1, 1), "#,##0")
     
'***Labour Hours (Work) / Non-labour hours (Equip)
     Dim dTarget_work_qty As Variant, dAct_work_qty As Variant, dRemain_work_qty As Variant, dComp_work_qty As Variant
     Dim dTarget_equip_qty As Variant, dAct_equip_qty As Variant, dRemain_equip_qty As Variant, dComp_equip_qty As Variant

     strsql = "SELECT Sum([target_work_qty]) FROM [TASK$] where cstr([proj_id]) = " & ProjectID & ""
     dTarget_work_qty = QueryResultArray(strsql)
     dTarget_work_qty = Format(dTarget_work_qty(1, 1), "#,##0.00")
     
     strsql = "SELECT Sum([act_work_qty]) FROM [TASK$] where cstr([proj_id]) = " & ProjectID & ""
     dAct_work_qty = QueryResultArray(strsql)
     dAct_work_qty = Format(dAct_work_qty(1, 1), "#,##0.00")

     
     strsql = "SELECT Sum([remain_work_qty]) FROM [TASK$] where cstr([proj_id]) = " & ProjectID & ""
     dRemain_work_qty = QueryResultArray(strsql)
     dRemain_work_qty = Format(dRemain_work_qty(1, 1), "#,##0.00")
     
     dComp_work_qty = Format(dAct_work_qty + dRemain_work_qty, "#,##0.00")
      
     strsql = "SELECT Sum([target_equip_qty]) FROM [TASK$] where cstr([proj_id]) = " & ProjectID & ""
     dTarget_equip_qty = QueryResultArray(strsql)
     dTarget_equip_qty = Format(dTarget_equip_qty(1, 1), "#,##0.00")
     
     strsql = "SELECT Sum([act_equip_qty]) FROM [TASK$] where cstr([proj_id]) = " & ProjectID & ""
     dAct_equip_qty = QueryResultArray(strsql)
     dAct_equip_qty = Format(dAct_equip_qty(1, 1), "#,##0.00")
     
     strsql = "SELECT Sum([remain_equip_qty]) FROM [TASK$] where cstr([proj_id]) = " & ProjectID & ""
     dRemain_equip_qty = QueryResultArray(strsql)
     dRemain_equip_qty = Format(dRemain_equip_qty(1, 1), "#,##0.00")
     
     dComp_equip_qty = Format(dAct_equip_qty + dRemain_equip_qty, "#,##0.00")
     
'***Project cost********************************************************

     Dim ddef_cost_per_qty As Double, def_cost_per_qty As Variant
     Dim dBudget As Double, dAct_cost As Double, dRemain_cost As Double, dComp_cost As Double
     strSql1 = "SELECT [def_cost_per_qty] from [PROJECT$] where cstr([proj_id]) = " & ProjectID & ""
     def_cost_per_qty = QueryResultArray(strSql1)
     ddef_cost_per_qty = def_cost_per_qty(1, 1)
     
     If TASKRSRC = False Then
             strsql = "SELECT [task_id], sum(([target_work_qty]+[target_equip_qty])* " & ddef_cost_per_qty & " ) AS [Sum_target_cost]," & _
             "sum(([act_work_qty]+[act_equip_qty])* " & ddef_cost_per_qty & " ) AS [Sum_Act_cost], " & _
             "sum(([remain_work_qty]+[remain_equip_qty])* " & ddef_cost_per_qty & " ) AS [Sum_Remaining_cost], " & _
             "val([Sum_Act_cost])+val([Sum_Remaining_cost]) AS [Sum_Completion_cost]" & _
             "from [TASK$] where cstr([proj_id]) = " & ProjectID & " GROUP BY [TASK$].[task_id] "
     End If
     If TASKRSRC = True Then
            strSQL2 = "SELECT [task_id],sum(val([target_cost])) AS [Sum_target_cost]," & _
              "sum(val([act_ot_cost])+ val([act_reg_cost])) AS [Sum_Act_cost],sum(val([remain_cost])) " & _
              "AS [Sum_Remaining_cost],val([Sum_Act_cost])+val([Sum_Remaining_cost]) AS [Sum_Completion_cost]" & _
              "from [TASKRSRC$] where cstr([proj_id]) = " & ProjectID & " GROUP BY [task_id] "

             strSQL3 = "SELECT [TASK$].[task_id], sum(([target_work_qty]+[target_equip_qty])* " & ddef_cost_per_qty & " ) AS [Sum_target_cost]," & _
             "sum(([act_work_qty]+[act_equip_qty])* " & ddef_cost_per_qty & " ) AS [Sum_Act_cost], " & _
             "sum(([remain_work_qty]+[remain_equip_qty])* " & ddef_cost_per_qty & " ) AS [Sum_Remaining_cost], " & _
             "val([Sum_Act_cost])+val([Sum_Remaining_cost]) AS [Sum_Completion_cost]" & _
             "from [TASK$] LEFT JOIN [TASKRSRC$] ON [TASK$].[task_id] = [TASKRSRC$].[task_id] WHERE isnull([TASKRSRC$].[task_id]) and cstr([TASK$].[proj_id]) = " & ProjectID & " " & _
             "GROUP BY [TASK$].[task_id] "


             strSQL4 = "SELECT   t1.[task_id], ([target_work_qty]+[target_equip_qty]-t2.[sum_target_qty]) * " & ddef_cost_per_qty & "   AS [Sum_target_cost], " & _
             "([act_work_qty]+[act_equip_qty]-t2.[sum_act_qty])* " & ddef_cost_per_qty & "  AS [Sum_Act_cost], " & _
             "([remain_work_qty]+[remain_equip_qty]-t2.[sum_remain_qty])* " & ddef_cost_per_qty & "  AS [Sum_Remaining_cost], " & _
             "val([Sum_Act_cost])+val([Sum_Remaining_cost]) AS [Sum_Completion_cost]" & _
             "from [TASK$] as t1 INNER JOIN (SELECT [task_id], sum([target_qty]) as [sum_target_qty], sum(val([act_ot_qty])+val([act_reg_qty])) as [sum_act_qty],sum([remain_qty]) as [sum_remain_qty]  from [TASKRSRC$] where cstr([proj_id]) = " & ProjectID & " GROUP BY [task_id] ) as t2 " & _
             " ON t1.[task_id] = t2.[task_id]"

             strsql = strSQL2 & " UNION ALL " & strSQL3 & " UNION ALL " & strSQL4
     End If
     If PROJCOST = True Then
             strSQL5 = "SELECT [task_id],[target_cost] AS [Sum_target_cost],[act_cost] AS [Sum_Act_cost] " & _
             " ,[remain_cost] AS [Sum_Remaining_cost], val([act_cost]) + val([remain_cost]) AS [Sum_Completion_cost] from [PROJCOST$] where cstr([proj_id]) = " & ProjectID & ""
             strsql = strsql & " UNION ALL " & strSQL5
     End If
     strSQLFinal = "SELECT  sum(val([Sum_target_cost])), sum(val([Sum_Act_cost])), sum(val([Sum_Remaining_cost])), sum(val([Sum_Completion_cost]))" & _
                " from (" & strsql & ")  "
          
     
     Dim ProjectCost As Variant
     ProjectCost = QueryResultArray(strSQLFinal)
     dBudget = Format(ProjectCost(1, 1), "#,##0.00")
     dAct_cost = Format(ProjectCost(1, 2), "#,##0.00")
     dRemain_cost = Format(ProjectCost(1, 3), "#,##0.00")
     dComp_cost = Format(ProjectCost(1, 4), "#,##0.00")
     Erase ProjectCost
'***Delete worksheets*********************
    Application.EnableEvents = False
    Call DeleteWorksheet("PROJECT")
    Call DeleteWorksheet("TASK")
    Call DeleteWorksheet("CALENDAR")
    Call DeleteWorksheet("TASKRSRC")
    Call DeleteWorksheet("PROJCOST")
    Call DeleteWorksheet("PROJWBS")
'***Populate with the result.
    With wss
        .Cells(1, 1) = "P6 Summary"
        .Cells(2, 1) = "Date Format": .Cells(2, 2) = Dateformat
        .Cells(3, 1) = "Project Name": .Cells(3, 2) = ProjectName
        .Cells(4, 1) = "Project ID": .Cells(4, 2) = ProjectID
        Call TableFormat("P6 Summary", 1, 1, 8, 2, 4)
        .Cells(1, 3) = "Schedule"
        .Cells(1, 4).EntireColumn.NumberFormat = Dateformat
        .Cells(2, 3) = "Data Date": .Cells(2, 4) = Format(DataDate, Dateformat)
        .Cells(3, 3) = "Project Start": .Cells(3, 4) = Format(StartDate, Dateformat)
        .Cells(4, 3) = "Project Finish": .Cells(4, 4) = Format(EndDate, Dateformat)
        Call TableFormat("P6 Summary", 1, 3, 8, 2, 4)
        .Cells(1, 5) = "Task Summary": .Cells(1, 6) = ""
        .Cells(1, 6).EntireColumn.NumberFormat = "#,##0"
        .Cells(2, 5) = "Activity total": .Cells(2, 6) = lTask
        .Cells(3, 5) = "WBS summary": .Cells(3, 6) = lTT_WBS
        .Cells(4, 5) = "Level of Effort": .Cells(4, 6) = lTT_LOE
        .Cells(5, 5) = "Start Milestone": .Cells(5, 6) = lTT_Mile
        .Cells(6, 5) = "Finish Milestone": .Cells(6, 6) = lTT_FinMile
        .Cells(7, 5) = "Task Dependent": .Cells(7, 6) = lTT_Task
        .Cells(8, 5) = "Resource Dependent": .Cells(8, 6) = lTT_Rsrc
        Call TableFormat("P6 Summary", 1, 5, 8, 2, 0)
        .Cells(1, 7) = "Activity Progress": .Cells(1, 8) = ""
        .Cells(1, 8).EntireColumn.NumberFormat = "#,##0"
        .Cells(2, 7) = "Completed": .Cells(2, 8) = lTK_Complete
        .Cells(3, 7) = "In Progress": .Cells(3, 8) = lTK_Active
        .Cells(4, 7) = "Not StartedE": .Cells(4, 8) = lTK_NotStart
        Call TableFormat("P6 Summary", 1, 7, 8, 2, 4)
        .Cells(1, 9) = "Criticality": .Cells(1, 10) = ""
        .Cells(1, 10).EntireColumn.NumberFormat = "#,##0"
        .Cells(2, 9) = "Completed (No Float)": .Cells(2, 0.1) = lNo_Float
        .Cells(3, 9) = "Negative Float": .Cells(3, 10) = lNeg_Float
        .Cells(4, 9) = "Critical Activities": .Cells(4, 10) = lCritical_Path
        .Cells(5, 9) = "Float <= 45d": .Cells(5, 10) = lCritical_Act
        .Cells(6, 9) = "Float > 45d": .Cells(6, 10) = lNon_critical
        Call TableFormat("P6 Summary", 1, 9, 8, 2, 2)
        .Cells(1, 11) = "Labour Hours": .Cells(1, 12) = ""
        .Cells(1, 12).EntireColumn.NumberFormat = "#,##0.00"
        .Cells(2, 11) = "Budget": .Cells(2, 12) = dTarget_work_qty
        .Cells(3, 11) = "Actual": .Cells(3, 12) = dAct_work_qty
        .Cells(4, 11) = "Remaining": .Cells(4, 12) = dRemain_work_qty
'        .Cells(5, 11) = "Actual + Remaining": .Cells(5, 12) = dComp_work_qty
        Call TableFormat("P6 Summary", 1, 11, 8, 2, 4)
        .Cells(1, 13) = "Non-Labour Hours": .Cells(1, 14) = ""
        .Cells(1, 14).EntireColumn.NumberFormat = "#,##0.00"
        .Cells(2, 13) = "Budget": .Cells(2, 14) = dTarget_equip_qty
        .Cells(3, 13) = "Actual": .Cells(3, 14) = dAct_equip_qty
        .Cells(4, 13) = "Remaining": .Cells(4, 14) = dRemain_equip_qty
'        .Cells(5, 13) = "Actual + Remaining": .Cells(5, 14) = dComp_equip_qty
        Call TableFormat("P6 Summary", 1, 13, 8, 2, 4)
        .Cells(1, 15) = "Project Cost": .Cells(1, 16) = ""
        .Cells(1, 16).EntireColumn.NumberFormat = "#,##0.00"
        .Cells(2, 15) = "Budget": .Cells(2, 16) = dBudget
        .Cells(3, 15) = "Actual": .Cells(3, 16) = dAct_cost
        .Cells(4, 15) = "Remaining": .Cells(4, 16) = dRemain_cost
'        .Cells(5, 15) = "Actual + Remaining": .Cells(5, 16) = dComp_cost
        Call TableFormat("P6 Summary", 1, 15, 8, 2, 4)
        .Cells(1, 1).Resize(1, 16).EntireColumn.AutoFit
        For i = 1 To 8
            If .Cells(1, i * 2).EntireColumn.ColumnWidth < 5 Then
                .Cells(1, i * 2).EntireColumn.ColumnWidth = 5
            End If
        Next i
    End With
    Application.EnableEvents = True
    Application.StatusBar = False
End Sub

3. Data Processing using SQL Connection

Data processing requires mutiple data tables and thus data processing with VBA programming language (array, looping and so on) requires extensive coding effort. SQL can make the coding work much simpler using SQL Connection for Excel VBA than VBA programming.
This is possible by using the ADO connect to Excel driver or Access Database. The current ADO connect is based on the ADO connect to Access Database and late binding to the needed object libraries.

Sql query string is passed to the Function QueryResultArray() below to handle the SQL query like Microsoft Access or SQL server.

Sub ExampleSQL()
'An Example SQL Query as a stirng
	strsql = "SELECT Sum([act_work_qty]) FROM [TASK$] where cstr([proj_id]) = " & ProjectID & ""
    dAct_work_qty = QueryResultArray(strsql)
    dAct_work_qty = Format(dAct_work_qty(1, 1), "#,##0.00")
End Sub
Function QueryResultArray(sqlquery As String) As Variant '4. Microsoft.ACE.OLEDB-Late Binding
    Dim rst As Object
    Dim MyConnect As String
    Dim arrResults() As Variant, i As Long, n As Long
    
    DoEvents
    Set rst = CreateObject("ADODB.Recordset")
    MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=" & ActiveWorkbook.FullName & ";" & _
               "Extended Properties=Excel 12.0"
    With rst
        .Open sqlquery, MyConnect
        .MoveFirst
        n = 0
        Do Until .EOF
             n = n + 1
             .MoveNext
        Loop
        Erase arrResults
        ReDim Preserve arrResults(n, .Fields.Count)

       .MoveFirst
        n = 0
        Do Until .EOF
            n = n + 1
            For i = 1 To .Fields.Count
                On Error Resume Next
                arrResults(n, i) = .Fields(i - 1)
                On Error GoTo 0
            Next i
            .MoveNext
        Loop
    End With
    QueryResultArray = arrResults
    Set rst = Nothing
    MyConnect = ""
End Function

4. Project planning data using the Xer file

On top of this page, the following pages can help you read/parse Xer file to Excel, consolidate project data and create project calendar and to do so, visit the pages below.

P6-project summary based on p6 xer file
P6-Project activity data table based on p6 xer file
P6-project calendar based on p6 xer file

5. Download an application package designed for Xer data manipulation

The downloadable application covers four(4) pages:Read/parse Xer data, P6 summary, P6 data table, and P6 Calendar.

Download Excel add-in and a workbook for demonstration

6. Loading Excel Add-in

It is necessary to install the downloaded Excel Add-in. To do so, visit the page below.

Create and load Excel Add-in

Modified on:3-November-2019
Dated on: 4-October-2019