Useful Excel VBA code to create documentation of Control Systems valuable for migrations. Reads multiple text or source code and organizes the info in Excel

Read txt files or source code in Excel, organize the information, this is a excellent tool when you want to migrate control systems.

In the next example, I am using a backup file generated by the control system and extracting and organizing the information in Excel.

To read this article in Spanish, please visit: www.guiaautomatizacionindustrial.blogspot.com

Documentation of the information contained on control system export files or backup files (useful for migrations).

Sometimes you are able to open the export files or backup files from a control system using a text editor like notepad, WordPad, etc. This can help us to analyze the information contained on these files and understand what is the control logic and be able to get an interpretation. But it is difficult to get a general idea about how many I/O or how many monitors, regulators, motors, calculations or on/off valves the system has. With an excel table we can get all this information that will be very useful, especially for migrations.

With the correct VBA Excel code, we can get the configuration of each tag type and its parameters.

For example, the next code search on the database the tags that have code or calculations and return the references this calculation is using.

To open the source code in Excel, first we have to define the location where the files are saved.

Type the paths on the column A of your spreadsheet.

Then, click on the tab Developer, and select Visual Basic, this will open the Visual Basic compiler. The tree on the left shows the sheets that you workbook has, select the sheet that has the list of locations and write the next code:

Sub Read_File_Text()

Dim archive As String   'path and name of the file en the list

Dim text As String     'Code line to be read

Dim row As Integer     'pointer of the row where the text will be placed on the spreadsheet

Dim rowCL As Integer   'pointer of the row where the file name will be placed

Dim column As Integer  'pointer of the column where the text will be placed

row = 1

rowCL = 1

ActiveSheet.Range("A2").Select     

Cells(1, 2).Value = "ID_CL"         'ID_CL row title

Do While Not IsEmpty(ActiveCell)    'Loop to read the location list

    row = row + 1

    rowCL = rowCL + 1

    archive = ActiveCell.Value      'assign variable archive to teh active control cell

    Open archive For Input As #1    'reads the archive in the input #

    column = 2                     'the names of the archives are placed on the column 2

    'Read and organize the content of the file

    While Not EOF(1) 'the EOF function returns the true when it reach the end of the file

        'Column 2 with the names of the files

        If archive Like "*CL Files*" Then

            archive = Replace(archive, "C:\CL Files\Folder1", "")

        End If

        Cells(rowCL, 2).Value = archive

        Line Input #1, text  'reads each line of the archive and it is saved on the variable text

        'Filter the text and look just for the word External

        If text Like "*EXTERNAL*" Then

            'Remove the string External from the text filtered.

            text = Right(text, Len(text) - InStr(text, "EXTERNAL") + 1)

            text = Replace(text, "EXTERNAL ", "")

            'Remove any other space on left of the code line

            Do While Left(text, 1) = " "

                text = Replace(text, " ", "")

            Loop

            column = column + 1

                If text Like "* *" Then

                    text = Left(text, InStr(text, " ") - 1)   

                End If

        'Placed the text on the corresponding cell

        Cells(row, column).Value = text

        Cells(1, column).Value = "EXTERNAL_REF" & column - 2

        End If               

    Wend   

    Close #1

ActiveCell.Offset(1, 0).Select

Loop

End Sub

This VBA Excel code will generate a spreadsheet with the list of external references that the code logics have.

Documentation of control system logic in Excel

Manipulating this VBA code, you could create Excel spreadsheets for other data types, like AI (analog inputs), DI (discrete inputs), PID (regulators), motors and valves and list each of their parameters.