Excel application scope write range

Help Activities
, , ,
1

I have excel sheet containing multiple columns having data like date, text, numeric
How can i preserve text of all these when i use excel application scope write range to write it to another excel

2

Hi @Shilpa_Mohanty

If you need to pass the entire from one sheet to another you should use first “Read Range activity” in the first sheet, and use “Write Range” in the other sheet. If you needs to preserve the format you have to convert in from your datatable after writing it.

hope this link helps something

Regards!

3

Hey @fernando_zuluaga
I have many columns like text, some are date format.
I want to preserve all the column format
And preserve format takes too much time coz I have 40k data
What are the other alternatives I can use
At end I have to filter date column and paste all the data to another sheet

4

Not sure how the output file is generated.

But you can create an output template and have all the columns with required datatype

At the end of the process, just copy and rename the file and then paste the data in the desired sheet

This will make sure the datatype is exactly as you want

5

@rahulsharma
May I know how to create the template
Or use a for each row and add data row

6

If you are writing to a new excel then we can try with COPY FILE activity instead while will retain the format completely

Use a copy file activity and in FROM property mention your current file path and in TO property mention the new filepath

Cheers @Shilpa_Mohanty

7

@Palaniyappan
Actually first I have to filter and then paste the filtered rows with header to a new excel using write range of excel application scope

8

Fine

Then in that case

  1. First copy the current file and paste it in another location

  2. Open that file and remove the data alone so that the format of the columns remains as a template

  3. Then get back to your original where use a excel application scope and pass the file path as input and get data with the help of read range activity with PRESERVE FORMAT ENABLED and get the output named dt

  4. Now use a filter datatable activity and get the output again as dt

  5. Now use a APPEND RANGE activity and pass the filepath of the copied file without any data as that is our template now

  6. Reason why we use append range is we have the template ready and headers ready as well
    So this new filtered data will just get appended to the current template

Cheers @Shilpa_Mohanty

9

@Palaniyappan
As the initial data table will come from other flow and we are not allowed to tick preserve format
That’s the problem
And preserve format takes more time to read data

10

Hi
Other workflow is still fine as it is anyhow invoked with your current flow and you would have used a read range activity to read that file I believe. There you can make it enabled.

And well not really
It’s the same time even if it is not enabled

11

No
We can’t use read range
Read range would be read by other flow and I would get only the data table as input
But I have to filter the date column first and then create a new excel and paste this datatable using write range of EAS
So while pasting the format gets changed

12

Fine
Where that workflow is ran, the one which reads the excel
Is it a separate process

@Shilpa_Mohanty

13

@Palaniyappan
Yes

14

Fine
May I know how the DATATABLE variable is passed across those different process

@Shilpa_Mohanty

15

Yes that dt variable is passed across multiple xamls
In my flow I have to read that dt
Filter according to date
And create a new file to paste the filtered data

16

I understand your query but I would like to know how that DATATABLE is passed between process

There are only two ways to do it

  1. Either it has to be held in one process with two different workflows and from workflow A it has been passed as argument to next workflow B

Or

  1. It has to be passed as argument through api from workflow A

Which one you are dealing with now
@Shilpa_Mohanty

17

It’s passed as an argument

19

Template file means just an Excel file with the formatting done…

In that make all the columns as per your requirement (column datatype, width, etc) then store in the project folder itself

At the end of process while creating a output file, simply copy that template and paste the data using Write Range in the target sheet and make sure you uncheck the the checkbox in properties panel and have the Range as A2

The below link is for the mini-tutorial for output report enchantment and it has a workflow that shows the above-mentioned steps. Just make the template as per your requirement.

Output Report Formatting using a Template

Hope this helps!