Change format of Excel sheet Columns to Text UiPath

A lot of times while working with excel automation we may need to change the format of excel sheet columns to Text.

Let’s say you have a column where the format is number or general but while using that at a particular step in your process automation.

That needs to be converted into text format to continue the execution without any error.

In this article, we will see how we can change the format of excel sheet columns to Text using UiPath.

Let’s get into the practical implementation of this task:

Step1: The input for this example and the column format is shown below:

Step2: Drag and Drop Excel application scope from activities panel to designer panel as shown below:

Step3: Drag and Drop Invoke VBA activity from activities panel to designer panel inside the excel application scope as shown below:

The required parameters for Invoke VBA Activity are shown below:

Here:

• CodeFilePath – Provide the file path where you have the VBA code.
• EntryMethodName – Provide the Method name that is used in the code.
• EntryMethodParameters – If you have any parameters then provie them here.
• OutputValue – Provide a variable that holds the output value of VBA code.

The VBA code to change the format of a single column is shown below:

Sub ChangeFormat() 
    Range("B1:B20").NumberFormat="@"
End Sub 

The VBA code to change the format of whole excel sheet columns is shown below:

Sub ChangeFormat()
    Dim sh As Worksheet
    Set sh= ThisWorkbook.Sheets("Sheet1")
    sh.UsedRange.NumberFormat="@"
End Sub

Here it is changing the format of columns in Sheet1.

If you want to change the format of any other sheet you can change the sheet name here.

Step4: The output of the above sequence is shown below:

Happy Learning!