Excel - Format column to text before writing

Help
1

How can I format the full column in excel to text and then append data to it.

2

@mohammedamaan,

I don’t think we have activities to do that, but if you have few rows of data and (I hope you are trying to write a number into fields and they are converting to some other formats ), you can directly paste the values by appending “'” , I mean (') a single quote before the data so that it will write as it is.

If you have huge data, or anyway you want to change the format and you want to write the values, here is a piece of code which will help you in changing the formats

   Dim excel As Microsoft.Office.Interop.Excel.Application
    Dim wb As Microsoft.Office.Interop.Excel.Workbook
    Dim ws As Microsoft.Office.Interop.Excel.Worksheet
    excel = New Microsoft.Office.Interop.Excel.Application
    wb = excel.Workbooks.Open(PathOfFile, [ReadOnly]:=False) 
    excel.Visible = True
    ws = CType(wb.Sheets(Sheet Name), Microsoft.Office.Interop.Excel.Worksheet)
    ws.Activate()
    ws.Range("F:F").NumberFormat = "@"
    wb.Save()
    wb.Close()
    excel.Quit()
1 Like
3

How can I append quote as I am writing the data in the sheet from data table

4

Use for each row activity, get the value of the column using the column name as row(“number”).tostring and assign it to a variable of type string as

variable = " ’ " + row(“Number”).tostring

please note, you dont need to give space for the single quote and value. give it as “'”, for your understanding, i gave space there

5

Well… Thanks. But I think this is not the right approach for me as I need to upload this data into a system.

I believe while uploading it will also consider the quote and it will fail so I think this approach might not help me.

6

Try doing to enter the data manually to the system once, I’m sure if the value you are getting is a number, it will retrieve the value as string without the quote.

Try appending quote to all the values and then copy and paste to the system.

I gave an alternative in the first solution, you can use that as well

7

I am a bit confused how to incorporate the first method. wwe need to use invoke code?

8

Yes, invoke code and paste the code in it . Change the path of the excel and the range which you want.

If you want to change the path as dynamic, try passing it as an argument to the code.

10

Getting this error

image

1 Like
11

Click on manage packages in the header and install the package as in the screenshot

image
image901×496 42.8 KB

12

11:52:51.1008 => [ERROR] [UiPath.Studio.Shared.Logging.OutputLogger] [35] Cannot load assembly: C:\Users\Amaan.nuget\packages\microsoft.office.interop.excel/15.0.4795.1000\lib/net20/Microsoft.Office.Interop.Excel.dll : System.IO.FileNotFoundException: Could not load file or assembly ‘office, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’ or one of its dependencies. The system cannot find the file specified.
File name: ‘office, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’
at System.Reflection.RuntimeAssembly.GetExportedTypes(RuntimeAssembly assembly, ObjectHandleOnStack retTypes)
at System.Reflection.RuntimeAssembly.GetExportedTypes()
at UiPath.Studio.Plugin.Workflow.Services.AssemblyContainer.AddAssemblyInternal(Assembly assembly, String path)
at UiPath.Studio.Plugin.Workflow.Services.AssemblyContainer.LoadAssemblies(String paths, IAppDomainCancellationToken cancellationToken)

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

13

File you are creating or giving the exisiting file?

14

getting this error when I am trying to install the package. It is not allowing me to install

15

This is fixed. It was due to office version.

17

Not working. It is not formatting

19

does this code creates a new file? as it is showing file already exist do you want to replace. Sorry I am not good at VBA

20

No, the code is to open the existing file and change the format of the specific range.

You want to create a new file in the code itself?

No problem @mohammedamaan, happy to help

21

No I don’t want to create a new file. When I am executing my process it says file already exist do you want to replace.

22

This is how my process is. Is it the right approach?
image

23

yes, you have to replace. Or instead of doing that manually, you can change the code as

  excel.Visible = False

that will help…

If you want to store it somewhere else instead of the same name, instead of

 wb.save()

use

   wb.SaveAs(Filepath and name)
1 Like