Rotate Excel Data

This process prompts the user to select an existing Excel file. It then creates a new worksheet, in the same file, that contains the columns of the first sheets as rows and vice versa.

function FileSelection
    Display.SelectFile Title:'Please select the Excel file' \
                    InitialDirectory:'' \
                    FileFilter:'*.xls*' \
                    IsTopMost:False \
                    CheckIfFileExists:True \
                    SelectedFile=> g:SelectedExcelFile \
                    ButtonPressed=> ButtonPressed
    if ButtonPressed='Cancel' then 
        exit
    end

end

function ExcelLaunchRead
    Excel.LaunchAndOpen Path:SelectedExcelFile  \
                    Visible:True \
                    Password:'' \
                    ReadOnly:False \
                    LoadAddInsAndMacros:False \
                    Instance=> g:ExcelInstance
    Excel.GetFirstFreeColumnRow Instance:ExcelInstance  \
                    FirstFreeColumn=> g:FirstFreeColumn \
                    FirstFreeRow=> g:FirstFreeRow
    Excel.ReadCells Instance: ExcelInstance  \
                    StartColumn:1  \
                    StartRow:1  \
                    EndColumn:FirstFreeColumn-1  \
                    EndRow:FirstFreeRow-1  \
                    ReadAsText:False \
                    FirstLineIsHeader:False \
                    Value=> g:ExcelDataTable
    Excel.AddWorksheet Instance: ExcelInstance  \
                    Name: 'Rotated Data'  \
                    WorksheetPosition:Excel.WorksheetPosition.First
    Excel.ActivateWorksheetByName Instance: ExcelInstance \
                    Name: 'Rotated Data'
end

function RotateLoop
    loop i from 1 to FirstFreeRow-1 step 1
        loop j from 1 to FirstFreeColumn-1 step 1
            Excel.WriteCell Instance: ExcelInstance \
                            Value: ExcelDataTable[i-1][j-1]  \
                            Column:i  \
                            Row: j
        end
    end
end

Call FileSelection
Call ExcelLaunchRead
Call RotateLoop
Display.ShowMessage Title:'Process Completed!' \
                    Message:'Process "Rotate Excel Data" completed.' \
                    Icon:Display.Icon.None \
                    Buttons:Display.Buttons.OK \
                    DefaultButton:Display.DefaultButton.Button1 \
                    IsTopMost:True \
                    ButtonPressed=> ButtonPressed

In more detail:


The process prompts the user to select the Excel file that will be edited, checks whether the user has selected a file (i.e. if the “Open” button in the “Select File Dialog” has been pressed. If so, it opens and reads the Excel file. Then, it adds a new worksheet where the data is written in rotary manner. In order the process to be better understood and debugged, it is divided into three functions: “FileSelection”, “ExcelLaunchRead” and “RotateLoop”.

Step 1: Prompt the User to Select an Excel File

Prompt the user to select the Excel file that will be edited. Check whether a file has been selected. If not, the process will be terminated.

function FileSelection
    Display.SelectFile Title:'Please select the Excel file' \
                    InitialDirectory:'' \
                    FileFilter:'*.xls*' \
                    IsTopMost:False \
                    CheckIfFileExists:True \
                    SelectedFile=> g:SelectedExcelFile \
                    ButtonPressed=> ButtonPressed
    if ButtonPressed='Cancel' then 
        exit
    end

end
  1. Create the function “FileSelection” that will contain the following actions. This is done using the term “function” followed by its name.
  2. Use the “Display.SelectFile” action to prompt the user to select an Excel file:
    1. Set the “FileFilter” argument to “*.xls*”, so that the user can only select a file that has an “xls” or “xlsx” extension.
    2. Set the “CheckIfFileExists” argument to “True”, so that only existing files can be accepted.
    3. Store the file in the “SelectedExcelFile” output variable. Mark the variable as global (i.e. “g:SelectedExcelFile”), as it will be used in another function.

  3. Use the “if” statement to examine whether the “ButtonPressed” output variable of the previous action is equal to “Cancel”. In this case, the user has not selected any file, so the process will need to terminate.
  4. Close the conditional block, and then the function, using the “end” statement.

Step 2: Open and Read the Selected Excel File

Open the selected Excel file, retrieve the first free cell, and read the data until the cell before that. Then, add a worksheet and activate it.

function ExcelLaunchRead
    Excel.LaunchAndOpen Path:SelectedExcelFile  \
                    Visible:True \
                    Password:'' \
                    ReadOnly:False \
                    LoadAddInsAndMacros:False \
                    Instance=> g:ExcelInstance
    Excel.GetFirstFreeColumnRow Instance:ExcelInstance  \
                    FirstFreeColumn=> g:FirstFreeColumn \
                    FirstFreeRow=> g:FirstFreeRow
    Excel.ReadCells Instance: ExcelInstance  \
                    StartColumn:1  \
                    StartRow:1  \
                    EndColumn:FirstFreeColumn-1  \
                    EndRow:FirstFreeRow-1  \
                    ReadAsText:False \
                    FirstLineIsHeader:False \
                    Value=> g:ExcelDataTable
    Excel.AddWorksheet Instance: ExcelInstance  \
                    Name: 'Rotated Data'  \
                    WorksheetPosition:Excel.WorksheetPosition.First
    Excel.ActivateWorksheetByName Instance: ExcelInstance \
                    Name: 'Rotated Data'
end
  1. Create the function “ExcelLaunchRead”.
  2. Use the “Excel.LaunchAndOpen” action to open the selected file:
    1. Set the “Path” argument to “SelectedExcelFile”, which is a global output variable set in the previous function.
    2. Store the Excel instance in the “ExcelInstance” variable and set this to be global, as it will be used in another function.

  3. Use the “Excel.GetFirstFreeColumnRow” action to retrieve the first free row and the first free column:
    1. Set the “Instance” argument to “ExcelInstance” , which is the output variable of the previous action.
    2. Set both the “FirstFreeColumn” and “FirstFreeRow” variables to be global.
  4. Use the “Excel.ReadCells” action to read the data and store it into a 2-dimension DataTable:
    1. Set the “Instance”argument to “ExcelInstance”.
    2. Set the “StartColumn” and “StartRow” arguments to “1”, so that the process will start reading the data from the first cell.
    3. Set the “EndColumn” and “EndRow” arguments to “FirstFreeColumn-1” and “FirstFreeRow-1” respectively, so that the process will read all cells containing data.
  5. Use the “Excel.AddWorksheet” action to add a new worksheet:
    1. Set the “Instance” argument to “ExcelInstance”.
    2. Set its name (e.g. “Rotated Data”).

  6. Activate the worksheet, using the “Excel.ActivateWorksheetByName” action:
    1. Set the “Instance” argument to “ExcelInstance”.
    2. Set the “Name” argument to the name specified in the previous action (i.e. “Rotated Data”).

Step 3: Write Data to Excel Worksheet

Write data in the new worksheet.

function RotateLoop
    loop i from 1 to FirstFreeRow-1 step 1
        loop j from 1 to FirstFreeColumn-1 step 1
            Excel.WriteCell Instance: ExcelInstance \
                            Value: ExcelDataTable[i-1][j-1]  \
                            Column:i  \
                            Row: j
        end
    end
end
  1. Create the “RotateLoop” function.
  2. As the action of writing data to a cell will need to be repeated for each cell of the Excel file, two loops will be used; one that will iterate over rows and another to iterate over columns:
    1. Loop that iterates over rows: “loop i from 1 to FirstFreeRow-1 step 1”.
    2. Loop that iterates over columns: “loop j from 1 to FirstFreeColumn-1 step 1”.

    Note that “FirstFreeRow” and “FirstFreeColumn” are global variables set in the previous function, indicating the location of the first free cell. This is used to count how many times the action of writing data to a cell will be repeated.

  3. Use the “Excel.WriteCell” action to write data to a specified cell:
    1. Set the “Instance” argument to “ExcelInstance”.
    2. Set the “Value” argument to “ExcelDataTable[i-1][j-1]”
    3. Set the “Column” and “Row” arguments to “i” and “j” respectively. This way, we rotate the data, as the “Column” will have as a value the rows’ index (i.e. “i”), and the “Row” will hold the value of the columns’ index (i.e. “j”).

Step 4: Call the Functions

Call the functions by using the term “call” followed by the function names, and inform the user when the process is completed.

Call FileSelection
Call ExcelLaunchRead
Call RotateLoop

Display.ShowMessage Title:'Process Completed!' \
                    Message:'Process "Rotate Excel Data" completed.' \
                    Icon:Display.Icon.None \
                    Buttons:Display.Buttons.OK \
                    DefaultButton:Display.DefaultButton.Button1 \
                    IsTopMost:True \
                    ButtonPressed=> ButtonPressed