How can I format the full column in excel to text and then append data to it.
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()
How can I append quote as I am writing the data in the sheet from data table
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
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.
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
I am a bit confused how to incorporate the first method. wwe need to use invoke code?
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.
Getting this error
11:52:51.1008 => [ERROR] [UiPath.Studio.Shared.Logging.OutputLogger]  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=184.108.40.206, Culture=neutral, PublicKeyToken=71e9bce111e9429c’ or one of its dependencies. The system cannot find the file specified.
File name: ‘office, Version=220.127.116.11, Culture=neutral, PublicKeyToken=71e9bce111e9429c’
at System.Reflection.RuntimeAssembly.GetExportedTypes(RuntimeAssembly assembly, ObjectHandleOnStack retTypes)
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].
File you are creating or giving the exisiting file?
getting this error when I am trying to install the package. It is not allowing me to install
This is fixed. It was due to office version.
Not working. It is not formatting
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
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
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.
This is how my process is. Is it the right approach?
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.SaveAs(Filepath and name)