Step-By-Step Guide to use Excel 365 Activity to Create Workbook and Add Worksheet to it

Let’s see an example to automatically create a workbook and worksheet at a specified location.

Below is the sample folder location where we want to create the workbook:

 

 

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 workbook and adding worksheet:

  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 define the file name with its extension to create the required Excel file.
    • WorkbookPath- an In argument to define the location where you want to create the required Excel file.
    • WorkbookNameforSheet- an In argument to define the file name with its extension along with its path.
    • SheetName- an In argument to define the required worksheet name.
      Position- an In argument to define the position where the worksheet must be created within the specified Excel file.
    • WorkbookOutput- an out argument to store the result of the Create Workbook feature.
    • WorksheetOutput- an Out argument to store the result of the Add Worksheet 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. From the Canvas Tools panel, add Application activity to the Flowchart designer.

 

 

  1. In the Application Type list, select Office365.
  2. In the Select an Application list, select the configured Office 365 application, Excel.
  3. 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 Workbook.
    1. Click the   (Setting) icon to configure the input configuration. The Input Configuration screen appears.
    2. Define the mandatory options:
    • Corresponding to the WorkBook Name parameter, select the WorkbookName argument in the Workflow Arguments list to pass the Excel file name (along with the file extension) that you want to create.
    • Corresponding to the Conflict Behaviour parameter, select the rename or fail as per your requirement. Here, we have selected rename.
    1. Define the advanced options:
    • Click Advanced Options and select Workbook Path.
    • Corresponding to the WorkBook Path parameter, select the WorkbookPath argument in the Workflow Arguments list to pass the location where you want to create the desired Excel file.

    1. Click Confirm.
  3. In the Output list, select WorkbookOutput to store the result of the Create Workbook operation.
    To view the Create Workbook 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 Workbook activity block.

 

  1. In the Access Token list, select Token argument.
  2. In the Feature list, select Add Worksheet.
    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 WorkBookNameforSheet argument in the Workflow Arguments list to pass the required Excel file. 
    1. Define the advanced options:
      • Click Advanced Options and select worksheet.
      • Corresponding to the worksheet parameter, select the SheetName argument in the Workflow Arguments list to pass the sheet name with which you want to create the worksheet.
      • Click Advanced Options and select position.
      • Corresponding to the position parameter, select the Position argument in the Workflow Arguments list to pass the position where want to create the sheet within the Excel file.

    2. Click Confirm.
  3. In the Output list, select WorksheetOutput to store the result of the Add Worksheet operation.

    To view the Add Worksheet 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 Workbook and Add Worksheet operations are successfully done:



    Observe first, the EmployeeDetails.xlsx file gets created at the specified location, and then the 2022 worksheet is added at position, 1.