How to change the whole excel sheet columns data type to text

[Manisree_Parasa]

Hi

I want to change the data type of the whole excel sheet into text. Can any one guide

[Palaniyappan (Palaniyappan P)]

Hi

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.

Cheers @Manisree_Parasa

[Manisree_Parasa]

@Palaniyappan, Thanks for the solution

[Manisree_Parasa]

@Palaniyappan , How to change the sheet name dynamically. Can we assign a variable for sheet name.

Thanks in advance