Template file property in Use Excel File

[salladinne (Karol Jankowski)]

Hello Team, can someone give an example usecase of Template file property in Use Excel File Activity? I see in documentation the official explanation:

• Template file - If the Excel file you want to automate is dynamic or does not exist at the time you design the automation (for example, the file is downloaded or created by the automation), select this option to use another Excel file that has the same structure at design time.
  The data in this file is available for selection from the Plus menu, allowing you to easily configure the activities added inside Use Excel File. The template file is used only at design time. When the automation is executed, it processes the file defined in the Excel file field.

However, it’s not yet clear for me when this could be used, and would happily listen about an example scenario, thanks

[jack.chan]

example scenario: . you are processing this table

id	name
123	ABC
233	DEF

for each row, you have to write the details + Date to an excel template

so you can use this file as the template

[salladinne (Karol Jankowski)]

@jack.chan - Thanks for quick reply. Couple of thoughts.

1. You either provide a template file path or the Excel file path in the Use Excel File properties? (in your example Excel file path is empty). I think that might be the case since if you would have both fields filled out, which one will refer to “Excel” from ‘Referance as’ field?
   1a. If both can be provided, can you provide a use case of when that could happen?
2. Will it primarily be used for Write to Files whenever you have previously obtained some data source (such as table, or any other) that you will iterate over and write into excel?
   2a. Since iteration is usually easier done on rule → extract DT → process/filter DT → write all DT as a whole back to excel sheet, rather than having to write line by line: Excel.Sheet(“Sheet1”).Range(“B”+counter.tostring) - is this feature really a time saver or rather something that is there but rarely used?

Thank you

[Rahul_Unnikrishnan]

Hello @salladinne

I think from the explanation itself the requirements of template is clear.

Just consider you have an excel with multiple sheets and each sheet is having some different headers. So in your Automation you are creating the excel file with todays date. But if the new excel should have all the sheets of the static template, then you can enable template file and provide the template file name.

Then whenever process creates a new excel file with todays date it will have all the sheets and headers as in the template.

[jack.chan]

@salladinne

You either provide a template file path or the Excel file path in the Use Excel File properties? (in your example Excel file path is empty). I think that might be the case since if you would have both fields filled out, which one will refer to “Excel” from ‘Referance as’ field?

no you provide both, it will make a copy of the template file then it will save it as the excel file path. Mine was empty as i was just giving as example.

1. Will it primarily be used for Write to Files whenever you have previously obtained some data source (such as table, or any other) that you will iterate over and write into excel?

yes, anything that requires a template.

2a. Since iteration is usually easier done on rule → extract DT → process/filter DT → write all DT as a whole back to excel sheet, rather than having to write line by line: Excel.Sheet(“Sheet1”).Range(“B”+counter.tostring) -

in the example i provided i write the details of each row into SEPARATE excels (1 for each row). Thats why the cell address is fixed. Below is a clearer version including for each row activity

is this feature really a time saver or rather something that is there but rarely used?

Time saver. Maybe your client wants data to be written to their template file where certain cells are highlighted / formatted etc. If you dont use a template then you have to format these cells by yourself in UiPath.

[Yoichi]

Hi,

If we use excel file dynamically, usually we set variable in ExcelFile. And in this case, we cannot choose sheet name etc using + sign, as the following.

However, if we set template file, we can choose sheet name, column name etc from template file even if excel file name is variable, as the following.

It will help to improve our productivity in design phase.

Regards,

[salladinne (Karol Jankowski)]

Thanks, that’s a bit clearer now