Excel - Classic Version - Column and Header Formatting

Help Studio
, ,
1

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

2

@PPIM_RPA

Try using copy sheet and then try modifying the data

cheers

3

Hello @PPIM_RPA

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

    image
    image752×508 15.8 KB

    Should :point_down:
    image
    image1014×519 26.3 KB

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

    image
    image758×555 20.5 KB

4

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”

image
image810×369 40.2 KB

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

5

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

6

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

image
image1171×430 24.4 KB

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?

7

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.

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.

8

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?
image
Thank you

9

@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.

10

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

11

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

12

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

13

Hi @PPIM_RPA Check below link

14

@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.

image
image1156×417 22.4 KB

A bit of enhancement was also done.

1 Like
15

@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:

image
image839×521 24.2 KB

In your version the header is fixed and columns autofit, but one of the rows is missing so the whole row is now highlighted:
image
image916×285 17.2 KB

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

16

@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 :
image

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

1 Like
17

Thank you so much @supermanPunch
Looks great

image
image914×524 25.8 KB

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

18

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

1 Like
19

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.