Excel Advanced-1.910.1120(2)

Owned by ARGOSLABS
Last updated: May 20, 2024
8 min read



Excel Advanced

You can read from and write to Excel over entire sheet or a range.

Primary Features
  • You do NOT need Microsoft Excel installed locally (the Plugin has the direct-handler-library integrated).
  • You must use .xlsx files.
  • You can read from .csv too.



Additional Notes - Overwrite Value only writes in String

When using Overwrite Value and Overwrite Cell in the Advanced menu, please note that Excel Advanced plugin enters all values including numerics as String. If you want a numeric type, there are 2 ways to achieve it - one is to use Excel Advance III (Convert Str2Num operation) or two is to use Excel Simple Write.



Need help?

Technical contact to tech@argos-labs.com


May you search all operations,

Contents

  1. Checkpoints
  2. No functions can be inserted
  3. Tips when reading from an Excel file
  4. Tips when writing to an Excel file
  5. How to use the Pivot option
  6. Return Value
  7. Data Only and Allow Overwrite options
  8. Other useful functions
  9. Remove Password




Attention

  • Please note that Return Value of Excel Advanced does NOT contain the output file's file-path!

  • EXCEL ADVANCED plugin is great for some tricky functions like finding a cell ID that includes a specific character string.
  • For general reading and writing, it is highly recommended to use EXCEL SIMPLE READ/WRITE plugins
  • Overwrite Value/Overwrite Cell enters value to "read-from" file(sheet), NOT "write-to" file(sheet).


1. Checkpoints

First here are the checkpoints when using Excel Advanced plugin!


  1. Main usage/purpose of this plugin is to “READ”. It would READ from an existing excel and store data internal to the Bot as CSV.
  2. This plugin is best when reading a “table” section only from an Excel file and prepare data for further processing.
  3. Caution --- Return Value is recommended to be either CSV or FILE. String will return the data itself, not the file path.
  4. WRITE menu is secondary but useful.
  5. When WRITE, the target Excel file needs not to exist but unless you need to write to an existing Excel file, we recommend using Return Value (.csv) and then convert it to .xlsx with other plugins such as File Conv.


THIS PLUGIN IS DESIGNED TO “READ” BEFORE “WRITE”.

Be careful! Return-Value stores data, not file path even when you “WRITE”.


  • Excel/CSV File: Must start with an existing Excel file to read from.
  • Read-fr sheet: IF not specified, it will read the entire data from the first sheet on left.
  • Write-to Excel file: WRITE features are secondary but helpful.
    When writing, the target Excel file needs not to be in existence. However, in this case the Return Value [String]WILL NOT return the file-path for the new file. It will return the data that was read from the first Excel. For continuous processes by handing off the file-path, one option is to use Return Value [File] and generate a new .csv file.





  • Excel/CSV File: Writes to this file
  • Write-to Excel file: Not this file
  • : About new Overwrite Value/Overwrite Cell
  • : These two parameters have been updated
  • : The name of the parameters and the location has changed.
    Previously, they were ---
    -Set Value
    -Set Cell
    These parameters write a “value” in a “cell” in the read-from file (either .xlsx or .csv), not to write-to file!
    The value cannot take formulas or functions – for that use either Excel Advance III or Excel Formula
    The cell can only take one cell. It cannot take a range such as A1:C10.







2. No functions can be inserted.


  • Note : Set Cell/Set Value only sets “values” in cells. It does not work to set “functions”. 






3. Tips when reading from an Excel file

  • You need a source file either xlsx or csv.
  • You can specify sheet and range.
  • You will read the data and store it in forms of String, CSV (Internal to bot), or File (usually CSV or TXT, external file location).


Right Text Bubble

  • No need for screenshot
  • Select path/file name to the target Excel file Must be .xlsx
  • Default is read from Sheet 1. You can define either the sheet name and/or range to read from Note: even reading from one-cell you have to a1:a1 

Left Text Bubble

  • When reading from Excel file, “Return Value” means, destination storage of the data that has been read.
    Options are
    • String
    • CSV (internally stored to bot)
    • File (usually .csv)
    In case of File(CSV), the target file does not have be present. It would be automatically created when none found. (The file will be overwritten when found.)








4. Tips when writing to an Excel file

  • You need a source file either xlsx or csv.
  • You can specify sheet and range to read from.
  • You specify a target file either xlsx or csv.
  • If target file is not found, the Excel Advanced plugin will automatically create the file.
  • If target file exists, the Excel Advance plugin will overwrite the specified cells.
  • The data will be simultaneously and store it in forms of String, CSV (Internal to bot), or File (usually CSV or TXT, external file location).

Right Text Bubble

  • No need for screenshot
  • Excel File: Select path/file name to the source file either xlsx or csv
    Or you can use .txt when the content is in a csv format. Must use .xlsx, .txt or .csv
  • Result Type: You can also store data as Return Value just like in the Read mode to;
    • String
    • CSV (data to internal variable)
    • File (external file)

Left Text Bubble

  • Write-to Excel file: When writing to Excel (xlsx), you must specify file path/name in full. The target file does not need to be present. The Plugin will create it if the file is not found.
    Caution: If the file IS present, the plugin will overwrite onto the existing file
  • Write start cell: You can specify what cell to start writing or what sheet to write the data to.
  • Keep Ext Blanks: More features are available such as setting a certain value to a certain cell or to handle blank cells as data, etc.

More tips about the Plugin [Return Value] types.






5. How to use the Pivot option

  • Here is your source EXCEL.

  • Pivot option will result in this Excel.


  •  The settings look like this.

Right Text Bubble

  • Entire path for the source Exel (.xlsx)
  • Set the range to read from
  • Entire path for the result Exel (.xlsx)
  • NOTE! Default is Sheet1

Left Text Bubble

  • Check on the Pivot option



  • If you want an internally stored CSV, the setting will look like this.

  • Choose CSV
  • Define “Group Name” for your plugin variable


Then your plugin variable {{test.A2(4)}} will produce E2.

Your row 1 will become headers. Row 2 to 5 are the data.

Plugin variable does not support 1 row CSV.





6. Return Values

  • Result Type : String

Variable name 
String will be stored internally.
You must set user-variable and assigned it here. (not an array)


  • Result Type : CSV

Group name
CSV will be stored internally.
No need for pre-setting the user-variable. Group name becomes the variable group name. Columns and rows are indicated as in example below.

{{group name.A(1)}}

Note: Column is uppercase only.


  • Result Type : File


Valuable name
In case, you desire to store the File path in a preset user variable, you can specify it here.

File path
File will be stored externally. It is either csv or txt. Full path need to be specified at File path. File does not need to be present. If the file not found, it would be generated automatically. If it is present, it would be overwritten.


7. “Data Only” and “Allow Overwrite” options

1. Data only

By checking this checkbox, all functions (formulas) will be deleted and the new sheet/book will only contain data (values).
WARNING! All functions and formulas will be deleted and lost forever.

2. Allow Overwrite
By checking this checkbox, the plugin will overwrite existing sheet/book and save it. The original sheet/book will be lost forever.
WARNING! This function can result in loss of functions/formulas.


  • Potential DATA LOSS features – BE CAREFUL
  • Check will delete all functions/formulas.
  • Check will allow overwriting existing sheet/book.




8. Other useful functions

1. Find String

    • Input
      Required
            - .xlsx or .csv file to read from.

      Optional
           - Sheet name
           - Cell ID/Range


  • .xlsx or .csv is required.
  • Sheet name is optional.
  • Cell ID and Range is optional.
  • These two parameters are used in pair.
  • Specify string to search.
  • Declare if allow partial match.



    • Output/Return Value
      Cell ID/Cell IDs in CSV format.


2. Pivot

    • Input
      Required
            - .xlsx or .csv file to read from
            - .xlsx or .csv file to write to

      Optional
           -  Sheet name of the source file.
           -  Cell ID/Range of the source file.
           -  Sheet name of the target file.
           -  Cell ID/Range of the target file.

      Even when you are writing the result to the same file, you must declare the target file.


  • .xlsx or .csv is required.
  • Sheet name is optional.
  • Cell ID and Range is optional.
  • Target file is required.
  • Sheet name is optional.
  • Cell ID and Range is optional.
  • By checking this option, the orientation of data will rotate 90 degrees.


    • Output/Return Value
      File path of the target file.


3. Show Data Range

    • Input
      Required
           - .xlsx or .csv file to read from

 
     Optional
          -  Sheet name
          -  Cell ID/Range

  • .xlsx or .csv is required.
  • Sheet name is optional.
  • Cell ID and Range is optional.
  • Return Value tells you the top right cell ID and the bottom right cell ID of your Excel data.


    • Output/Return Value
      Top left cell ID and bottom right cell ID that contains some data on the sheet.


4. Clear Cell

    • Input
      Required
           - .xlsx or .csv file to read from

      Optional
           -  Sheet name
           -  Cell ID/Range

  • .xlsx or .csv is required.
  • Sheet name is optional.
  • Cell ID and Range is optional.
  • WARNING! : By checking Clear Cell?, all the data will be deleted (and lost forever).
  • Output/Return Value
    Top left cell ID and bottom right cell ID that contains some data on the sheet.


9. Remove Password

1. When the Excel file has a password use this option.


2. 'Reomve Password' options have 2 functions

  • if you check the Remove Password option  

          - Remove the password on the file

  • if you uncheck the Remove Password option but you want to modify excelfile

             - Plz input the Edit Password and Write password

             - if you want to save a file with an orignal password. Enter the original password in the 'Write password'option

             - if you want to save a file with another password. Enter the another password in the 'Write password' option

Return Code

Code
Meaning
0Execution successful
1Execution failed