Excel cell formatting control

Hi Experts,

Does anyone has experience to automate cell formatting in Excel?
For example you want to change a decimal 1.25 to 1.3.
In Excel we manually do this through Format Cell panel:

image

*Write cell with round up function is also not the solution that we’re looking for.

Thank you.

Hi @D_Okthree ,

I know how to do it in VBA. please confirm for me, are you using studiox? if so please check if theres an activity called execute VBA.

if you are a studio or studio pro user i can send you the VBA code and give you instructions on how to run it in UiPath.

Hi,

I’m using Studio, however the Excel activity is StudioX.
There is no execute VBA activity, but it has Invoke VBA activity. Are they different?

image

Thanks.

Invoke VBA Should work…Will send you the code shortly

@D_Okthree

do you want to format all the cells in a worksheet or just specific ones? if so please give me the range e.g A1:B14

Hi,

It will be in specific cells. Thank you.

Instructions:

use the invoke VBA activity inside of excel scope activity

save the below code in a text file save it as .txt file

Sub reformat_cells(in_range As String)

    Range(in_range).Select
    Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"

End Sub

Invoke VBA activity parameters

Code File Path => the text file with the VBA Code
EntryMethodNme => the method or Sub name in the txt file => "reformat_cells"
EntryMethodParameters => this is the range you want to change the cell format to. Just change it in the assign activity

examples: “A1” or "G6:L11"

Please see example below, down load it and run it for yourself and let me know if it helps.

excel_format_cells.zip (8.4 KB)

Hi,

Thank you. Let me test this first and let you know the result.
A small thing, is this a Regex: “(* #,##0.00_);_(* (#,##0.00);($* “”-”"??);(@_)" ?

No. Its an excel formula for changing the cell format to accounting

Ok, thanks.

Hi,

I tried to use the VBA code and change code for format of cells. It’s get error of “mismatch type”. I use for decimal format (ex. 4.8). I changed the code to:

Sub reformat_cells(in_range As String)

Range(in_range).Select
Selection.NumberFormat = **"_(* #,##0.0_);_(* (#,##0.0);_(* "-"_);_(@_)"**

End Sub

I have followed the format decimal in Excel and it works. But it got error wit Invoke VBA in UIPath. Please advise.

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