Excel cell formatting control

Help StudioX
,
1

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.

2

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.

3

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.

4

Invoke VBA Should work…Will send you the code shortly

5

@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

6

Hi,

It will be in specific cells. Thank you.

7

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)

8

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);($* “”-”“??);(@_)” ?

9

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

10

Ok, thanks.

11

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.

12

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