I’m trying to read a excel file containing 3 lakh records in it and filtering the data table with some required columns. Then i’m trying to write the filtered table into new sheet. The issue is that , in the actual sheet, Column Name is product ID, my row value is 005300608 which is a string and when i copied the data into new sheet with write range activity, it is converted to 5300608 which is a integer. when i tried to make pivot on that, it is summing up the data in the product ID column as well.
I find a solution mentioned that, for each row , if we append a (') apostrophe, then the row data will be string. if i want to do that for all 3 lakh records, then the robot will run for 4 hours and there will be no use of automation here.
@skini76 Thanks for ur reply
I tried formatting the cells as text before running the robot process. Still getting the value as 5300608 in the new sheet.
Am i missing anything in the steps you mentioned?
Tried the way you mentioned above. Still getting the values as string i.e, preceeding zeroes are trimmed. And i tried to write the columns to a .CSV and then to write in the excel. Still the values in the CSV are not as required .
When you try to write it from datatable to excel, it will write it in the number format instead of string. So, if you have huge number of rows, try to copy and paste special with a piece of VB code which paste special will always include the format .
Dim excel As Microsoft.Office.Interop.Excel.Application
Dim excelDiscount 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(Path of excel, [ReadOnly]:=False)
excel.Visible = True
ws = CType(wb.Sheets("MT"), Microsoft.Office.Interop.Excel.Worksheet) 'select a sheet and activiates'
Dim MTUsedColumnsRange As Integer = ws.UsedRange.Columns.Count
Dim newSheet As Microsoft.Office.Interop.Excel.Worksheet = Nothing
newSheet = CType(wb.Sheets.Add, Microsoft.Office.Interop.Excel.Worksheet)
newSheet.Name = "MTSheet"
For c As Integer = 1 To MTUsedColumnsRange
Dim str As String = Convert.ToChar(64 + c)
If CType(ws.Cells(4, c), Microsoft.Office.Interop.Excel.Range).Value.ToString = "Project" Then
ws.Range(str + "4:" + str + (MTUsedRange - 1).ToString).Copy()
This is a good question you got there. This is not a solution to the activity itself but it might help you. Instead of using the write range from the Excel Scope, use the system write range, that one will not convert string to values. If you type write range in the activity panel you would see the one under App Integration for Excel and one under System for Workbook. Try using the one under system.