Append Line To Excel

This process prompts the user to select an existing Excel file (.xls or .xlsx). It then adds text at the first cell of the first free row and column.

Display.SelectFile Title:'Please select the Excel file that you want to edit.'\
                    InitialDirectory:'' \
                    FileFilter:'*.xls;*.xlsx' \
                    IsTopMost:False \
                    CheckIfFileExists:true \
                    SelectedFile=> SelectedExcelFile \
                    ButtonPressed=> ButtonPressed

if ButtonPressed='Open' then

	Excel.LaunchAndOpen Path:SelectedExcelFile \
                    Visible:True Password:'' \
                    ReadOnly:False \
                    LoadAddInsAndMacros:False \
                    Instance=> ExcelInstance

	Excel.GetFirstFreeColumnRow Instance:ExcelInstance \
                    FirstFreeColumn=> FirstFreeColumn \
                    FirstFreeRow=> FirstFreeRow

	Excel.WriteCell Instance:ExcelInstance \
                    Value:'This text has been added by robin!'  \
                    Column:FirstFreeColumn  \
                    Row: FirstFreeRow
    
        Excel.Save Instance: ExcelInstance \
    
        Excel.Close Instance: ExcelInstance 

	Display.ShowMessage Title:'' Message:'Some text has been added at the last row of the file: '+SelectedExcelFile \
                    Icon:Display.Icon.None \
                    Buttons:Display.Buttons.OK \
                    DefaultButton:Display.DefaultButton.Button1 \
                    IsTopMost:False \
                    ButtonPressed=> ButtonPressed
end

Display.ShowMessage Title:'Process Completed!' \
                    Message:'Process "Append Line to Excel" completed.' \
                    Icon:Display.Icon.None \
                    Buttons:Display.Buttons.OK \
                    DefaultButton:Display.DefaultButton.Button1 \
                    IsTopMost:False \
                    ButtonPressed=> ButtonPressed

In more detail:


The process prompts the user to select an Excel file and checks if the “Open” button is pressed. If so, it launches and opens the selected file, retrieves the first free row of the active worksheet and adds text to it. The file is then saved and closed.

Step 1: Prompt the User to Select a File

Prompt the user to select an Excel file.

Display.SelectFile Title:'Please select the Excel file that you want to edit.'\
                    InitialDirectory:'' \
                    FileFilter:'*.xls;*.xlsx' \
                    IsTopMost:False \
                    CheckIfFileExists:true \
                    SelectedFile=> SelectedExcelFile \
                    ButtonPressed=> ButtonPressed

Use the “Display.SelectFile”action:

  1. Set the Dialog title.
  2. Set the “FileFilter” argument to ‘*.xls;*.xlsx’ to make sure that only files with these extensions will be available for selection.
  3. Store the selected file in the “SelectedExcelFile” output variable.

Step 2: Add Text to the First Free Row and Column

Check whether the user has selected an Excel file (i.e. if the user pressed the “Open” button). If so, launch and open the file, get the first free row and column and add text to it.

if ButtonPressed='Open' then

	Excel.LaunchAndOpen Path:SelectedExcelFile \
                    Visible:True Password:'' \
                    ReadOnly:False \
                    LoadAddInsAndMacros:False \
                    Instance=> ExcelInstance

	Excel.GetFirstFreeColumnRow Instance:ExcelInstance \
                    FirstFreeColumn=> FirstFreeColumn \
                    FirstFreeRow=> FirstFreeRow

	Excel.WriteCell Instance:ExcelInstance \
                    Value:'This text has been added by robin!'  \
                    Column:FirstFreeColumn  \
                    Row: FirstFreeRow
  1. Use the “if” statement to check whether the “ButtonPressed” output variable of the previous action is equal to “Open”.
  2. Open the Excel File by using the “Excel.LaunchAndOpen” action. Set the “Path” argument to “SelectedExcelFile”, which is an output variable of the “Display.SelectFile” action (used in the first step).
  3. Get the first free row and column of the active worksheet by using the “Excel.GetFirstFreeColumnRow” action.
  4. Use the “Excel.WriteCell” action to write text to the first free cell found from the previous action:
    1. Set the “Instance” argument to “ExcelInstance”, which is the output variable of the “Excel.LaunchAndOpen” action.
    2. Set the “Value” argument to the desired text that will be inserted in the Excel file.
    3. Set the “Column” and “Row” arguments to “FirstFreeColumn” and “FirstFreeRow” respectively. These are the output variables of the “Excel.GetFirstFreeColumnRow” action, used above.

Step 3: Save and Close the Excel File

Save and close the Excel file, as well as inform the user that the process has been completed.

Excel.Save Instance: ExcelInstance \
    
    Excel.Close Instance: ExcelInstance 

	Display.ShowMessage Title:'' Message:'Some text has been added at the last row of the file: '+SelectedExcelFile \
                    Icon:Display.Icon.None \
                    Buttons:Display.Buttons.OK \
                    DefaultButton:Display.DefaultButton.Button1 \
                    IsTopMost:False \
                    ButtonPressed=> ButtonPressed
end

Display.ShowMessage Title:'Process Completed!' \
                    Message:'Process "Append Line to Excel" completed.' \
                    Icon:Display.Icon.None \
                    Buttons:Display.Buttons.OK \
                    DefaultButton:Display.DefaultButton.Button1 \
                    IsTopMost:False \
                    ButtonPressed=> ButtonPressed
  1. Use the “Excel.Save” and “Excel.Close” actions and set the “Instance” arguments to “ExcelInstance”.
  2. Use the “end” statement to close the condition block (i.e. if ButtonPressed = ‘Open’).
  3. Use the “Display.ShowMessage” action to inform the user when the process is complete.