Read and parse P6 Xer file to Excel with VBA

This page discusses how to read a text file with extension .xer in Primavera P6 schedule package and how to parse to Excel data.

1. Prompting for a Xer file using FileDialog () method

If you need to get a file name, you can use the GetOpenFileName method or FileDialog object. The sub procedure below uses the FileDialog object A selected file name including its path are stored in a worksheet of the Excel add-in or Windows registry for use in the next session. For the latter method, visit the page below. Register data of current session for use in next session

Sub Choose_XerFile()
    Dim FDialog As Office.FileDialog
    Set wba = Workbooks("PRAP.xlam")
    Set wsa = wba.Worksheets("xer")
    Set FDialog = Application.FileDialog(msoFileDialogFilePicker)
    On Error Resume Next
    With FDialog
        .InitialFileName = ActiveWorkbook.Path
        .AllowMultiSelect = False
        .Title = "Please select a file with .xer extension."
        .Filters.Clear
        .Filters.Add "XER", "*.xer"
        If .Show = True Then
            Filename = .SelectedItems(1)
        Else
            Filename = ""
        End If
    End With
    If Err <> 0 Then
        Exit Sub
    End If
    On Error GoTo 0
'Save the xer file name including it path in the Excel add-in for next session.
    wsa.Cells(1, 1).Value = Filename
End Sub

2. Importing data in a text file to an array named SData ( )

The code in the following example reads a text file and then places each line of data in the SData() array for further parsing each line data. Keep in mind that the SData() array is one dimensional array.

	Open Filename For Input As #1
    r = 0
    Do Until EOF(1)
        Line Input #1, data
        r = r + 1
        ReDim Preserve SData(r)
        SData(r) = data
    Loop
    Close #1

3. Parse Xer file and create the distinctive data array

The code in the following example reads a text file and then places each line of data in the SData() array for further parsing each line data. The separator used in Xer file is tab. The Xer file contains various and distinct data that are distinguished by %T followed by the relevant data catagory. Every distinct data is stored in the myTable() array.

	Dim a As Variant, UniqueTableRow() As Variant
    n = 0
    For i = 1 To UBound(SData)
        a = Split(SData(i), vbTab)
        If a(0) = "%T" Then
            n = n + 1
            ReDim Preserve UniqueTableRow(n)
            UniqueTableRow(n) = i
        End If
    Next i
    ReDim Preserve UniqueTableRow(n + 1)
    UniqueTableRow(n + 1) = r - 1
    
    Dim myTemp() As Variant
    ReDim Preserve myTable(n)
    For i = 1 To n
        DoEvents
        r = UniqueTableRow(i)
        a = Split(SData(r + 1), vbTab)
        cs = UBound(a)
        rs = UniqueTableRow(i + 1) - UniqueTableRow(i)
        ReDim myTemp(rs, cs)
        For j = 1 To rs
            a = Split(SData(r + j - 1), vbTab)
            On Error Resume Next
            For K = 1 To cs
                If a(K) <> "" Then
                    myTemp(j, K) = a(K)
                End If
            Next K
            On Error GoTo 0
        Next j
        myTable(i) = myTemp
    Next i	

4. Project planning data using the Xer file

You can create project high level summary, 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

Dated on: 4-October-2019