Excel - Classic Version - Column and Header Formatting

[PPIM_RPA]

Hello Community,

Header_Column Formatting to Appended Sheet.zip (866.1 KB)
See enclosed workflow. I am hoping to get the header style and column spacing in the appended sheet to match the style in the “Contract Tracker” sheet.
Have searched and searched, but still hoping this can be automated with Excel Classic version.
Thank you

[Anil_G (Anil Gorthi)]

@PPIM_RPA

Try using copy sheet and then try modifying the data

cheers

[Gokul_Jayakumar (Gokul Jai)]

Hello @PPIM_RPA

1. The Kill activity should use before the application opens. Shouldn’t use inside the scope activity.
   

   
   
   Should
   
   

2. Inside the Excel Application scope, can use the excel based activities, Dont needs to use the workbook activity.
   

   

[PPIM_RPA]

Thank you on the first point about Kill Process flow. Probably making all kinds of dumb mistakes like that.
As for the second, when I move Autofit Range underneath, I’m told I need a “Use Excel File”

Maybe I should just give up on getting autofit to work in Classic mode

[Gokul_Jayakumar (Gokul Jai)]

@PPIM_RPA

getting autofit to work in Classic mode

@PPIM_RPA
You can use invoke VBA method in excel scope to autofit the columns

VBA AutoFit (Rows, Column, or the Entire Worksheet)

How to auto fit columns in an excel using UiPath | Excel Automation | Invoke VBA | RPA

[PPIM_RPA]

Looks like this is the only “solution”. However, there is this IMPORTANT notice from UiPath:

What if this code is created and the settings are made in my Excel. Then, someone else run the file on their computer using a zip file. If they don’t enable these settings, will the bot break?

[supermanPunch (Arpan)]

@PPIM_RPA

Header_Column Formatting to Appended Sheet.zip (866.1 KB)
See enclosed workflow. I am hoping to get the header style and column spacing in the appended sheet to match the style in the “Contract Tracker” sheet.

Hi @PPIM_RPA ,

When enclosing the workflows, Do also enclose it’s project.json file, as there would be some incompatibilities or version changes that will be observed when also reverting back with our version of projects in place.

@PPIM_RPA

I am hoping to get the header style and column spacing in the appended sheet to match the style in the “Contract Tracker” sheet.

Is the Format going to be the same always ? If so, I believe you could keep a Template of that Excel sheet separately, then Before writing the modified data, use Copy File Activity, which would basically copy the Template file and rename it to your required format, then write the data to it using Write Range activity from the A2 range.

This should keep the Header/Column format the same.

[PPIM_RPA]

Think I’m getting close. I’ve created the template but want it to apply to an appended sheet on dt_FinalTable. Does copy file apply the template to all sheets, including appended ones or is another step needed?

Thank you

[supermanPunch (Arpan)]

@PPIM_RPA

Does copy file apply the template to all sheets, including appended ones or is another step needed

@PPIM_RPA ,

I believe for this case, we would require to use Copy Sheet activity and copy the sheet from the Template file to the Output file as you would have multiple sheets to append into the same file.

[PPIM_RPA]

Thank you all for your suggestions and advice.
Tried to follow along your tips but not exactly sure.
I thought I was close with the idea add the template as a sheet to one of my source documents.
After it was read and used for comparison, that sheet would be copied with format to the final version of the sheet. Didn’t work. Sigh. See enclosed code with copy sheet commented out.
Excel Template Copy Sheet.zip (902.6 KB)
Appreciate all the feedback

[Jefh (Jesus Fonseca)]

Hi, What u want to do, can you explain better please, adding captures and trying telling us what’s the trouble?

[PPIM_RPA]

Hi. Sorry for the confusion.
I am trying to use above workflow to get autofit columns on the “Uncommon Rows” appended sheet.
People have suggested using copy sheet that would read a template and provide that functionality.

It’s the presentation factor. The bot can successfully sort through 20,000 rows of data and provide the user the uncommon rows between two excel files, yet doesn’t have the ability to spread the data out (autofit) so it is not crammed together.

TLDR: looking for a successful way to get autofit working using Excel classic without invoke VBA

[ushu (Usha kiranmai)]

Hi @PPIM_RPA Check below link

How to AutoFit All Columns/Rows in Excel - UiPath

Hi everyone, If I need to autofit all columns in an excel sheet so how can I do that? First of all, you will have to enable the ‘Morden Activity’ option in UiPath. (UiPath.Excel.Activities.Business.AutoFitX - Changes the displayed column width and row height in the specified range to the widest entry in each column and the highest entry in each row.) [1] Use the ‘Excel Process Scope’ activity. Use the ‘Excel File’ activity. [2] Use the ‘Autofit Range’ activity. [3] Configuri…

[supermanPunch (Arpan)]

@PPIM_RPA ,

Could you Check the below modified workflow :
Header_Column Formatting to Appended Sheet.zip (874.1 KB)

As mentioned previously, we can prepare a Template based on the Excel sheet format required, then copying the sheet from this template to the First File, then after the Uncommon rows are found we write the data to this sheet starting from the Range A2, as the headers are already a part of the sheet.

A bit of enhancement was also done.

[PPIM_RPA]

@supermanPunch Thank you so, so much for your comments, review, and workflow.
The header issue is now fixed, however the output has changed when enhancements were made.
See below.
The bot’s purpose is to compare the two rows of uncommon output and highlight the differences:

In your version the header is fixed and columns autofit, but one of the rows is missing so the whole row is now highlighted:

Seeing that formatted header and autofit column when I ran this really brought a smile to my face.

[supermanPunch (Arpan)]

@PPIM_RPA ,

Apologies. It seems I made a small mistake.

Could you shift the datatables in the Merge Datatables Activity and check, It should be like the below image :

Let me know if you still receive the same output again.

[PPIM_RPA]

Thank you so much @supermanPunch
Looks great

I’m curious about the red highlighting in Column B and why it only appears in that one

[PPIM_RPA]

Found it. It was in the template. Thank you again