Step-By-Step Guide to use Excel 365 Activity to Create Blank Table and Write Data

Let’s see an example to automatically create an empty table inside an existing Excel 365 file and then write data available in an Excel file saved on the local machine.

Below is the sample Excel file saved on the local machine. It contains serial number, name of the city and respective city temperature:

 

 

Prerequisites:

  • Register the required Excel 365 application in Azure Active Directory.
  • Configure the Office 365 application in Automation Studio. You must add the Microsoft Service URL with an option to create a sign-in process. This lets you establish a connection between Office 365 application and Automation Studio to automatically sign in and perform the automation. Here we have configured Office 365 with the name, Excel.

 

 

  • Access token to authorize and login to Excel 365 application. The access token can be fetched using the Oauth activity. Here, we have fetched the access token into an Out argument, Token.

 

To automate the process of creating an empty table and writing data within it:

  1. Continue with the same automation process workflow where you have used Oauth activity to fetch the access token.

    Or

    Create a new process and use the Reuse Process activity to use the existing Oauth process workflow. Ensure that the Oauth process is published if you want to reuse the workflow. 
    Here we have continued with the Oauth process workflow itself.
  2. Create the following arguments:
    • WorkbookName- an In argument to pass the Excel 365 file name along with its path where you want to create the table.
    • WorkSheetName- an In argument to pass the worksheet name where you want to create the table.
    • TableName- an In argument to pass the table name that you want to create in the specified Excel 365 file.
    • CreateOutput- an out argument to store the result of the Create Table feature.
    • WriteOutput- an Out argument to store the result of the Write Range feature.

NOTE:  

In the Oauth activity, ensure to set the Scope to User.Read Files.ReadWrite.All permission to enable Automation Studio with the required permission to use Excel 365 application.

 

  1. Drag the Import DataTable activity and drop below the Oauth Activity. This lets you save the values in the data table format as Automation Studio supports passing the values only in the data table format. 
    1. Provide the Excel file path to import the required table.
    2. Select the sheet to import the required table.
    3. Enter a name of the Data Table to hold the imported table values. Here, we have entered MyDataTable. 
    4. Indicate if the Excel file has a header.
    5. Using the   (Settings) icon, configure the Readers and Header.
      Refer Import DataTable activity for detailed information.
  2. From the Canvas Tools panel, add Application activity to the Flowchart designer.
  3. In the Application Type list, select Office365.
  4. In the Select an Application list, select the configured Office 365 application, Excel.

 

 

  1. Drag the Excel 365 activity and drop inside the Office365 Application block.

 

 

  1. In the Access Token list, select Token argument.
  2. In the Feature list, select Create Table.
    1. Click the   (Setting) icon to configure the input configuration. The Input Configuration screen appears.
    2. Define the mandatory options:
    • Corresponding to the workbook parameter, select WorkBookName argument in the Workflow Arguments list to pass the required Excel file where the table needs to be created.
    • Corresponding to the worksheet parameter, select WorksheetName argument in the Workflow Arguments list to pass the required sheet name of the specified Excel file where the table needs to be collected.
    • In the address parameter, enter a1:c6 in the Workflow Arguments list and select the corresponding checkbox to make it as the default value. This indicates the column and row range for creating the table in the specified Excel file. You can provide the range as per your requirement. Here, we want to create a table with 3 columns ranging from A to C with 6 rows having first row as the header.
    • Corresponding to the name parameter, select TableName argument in the Workflow Arguments list to pass the required table.
    • Corresponding to the hasHeaders parameter, select true argument in the Workflow Arguments list to indicate that the specified Excel file has headers.

      By configuring the values parameter using the Advanced Options, you can pass and replicate the values stored in the imported Data Table to the table created in the required Excel 365 file. Here, we are creating an empty table; hence the Advanced Options is not used.

    1. Click Confirm.
  3. In the Output list, select CreateOutput to store the result of the Create Table operation.
    To view the Create Table result in Automation Studio, you can add WriteLine activity. This prints the result in the Studio Console Output tab after you test run the process. 

 

 

  1.  Drag the Excel 365 activity and drop below the Create Table activity block.

 

 

  1. In the Access Token list, select Token argument.
  2. In the Feature list, select Write Range.
    1. Click the   (Setting) icon to configure the input configuration. The Input Configuration screen appears.
    2. Define the mandatory options:
    • Corresponding to the workbook parameter, select WorkBookName argument in the Workflow Arguments list to pass the required Excel file. 
    • Corresponding to the worksheet parameter, select WorksheetName argument in the Workflow Arguments list to pass the sheet name of the specified Excel file.
    • In the address parameter, enter a2:c6 in the Workflow Arguments list and select the corresponding checkbox to make it as the default value. This indicates the column and row range for writing data in the specified Excel file. You can provide the range as per your requirement. 
    • Corresponding to the values parameter, select MyDataTable argument in the Workflow Arguments list to pass the values stored in the imported Data Table that must be written in the created table in the Excel 365 file. 

    1. Click Confirm.
  3. In the Output list, select WriteOutput to store the result of the Append Range operation.

    To view the Write Range result in Automation Studio, you can add WriteLine activity. It prints the result in the Studio Console Output tab after you test run the process. 

    You can assign this process to a robot if you want to execute this process outside Automation Studio.
  4. Save the process.
  5. Setup the environment and enter the required login details.
  6. Perform test run.
    Below is the sample screen shot of the Studio Output Console and the Excel 365 file after the Create Table and Write Range operations are successfully done:


    Observer first, a blank table with the same formatting as that of the Excel file saved on the local machines gets created, and then data is written within it: