I need to sort my data in excel according to column for example if i have the data in A column it should be in A column and level 1 if B column put the data in A column and level 2, if C column put data in A column and level 3 and so on so forth, (Level column will be created in B column after the filtration)
Could u help me with that
Here are the steps:
- Read your excel and store in the data table.
- Then use if condition to check which column has the data
- After detecting the data, copy the entire column data to column A
- Then use sort data table activity to sort the data
Hope this helps
how can i copy the whole data into A column ??
If you have less data, you can use for each row to write data from one row to another, If you have huge data, it will consume more time which is not the objective of automation.
So, what we can do is…
After detecting the data column, use sort data table on that column itself
Write the data table to excel using write range activity within the excel application scope.
Then, as we have the column index we can do that using Invoke code activity and here is the code which will help you
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("FileName", [ReadOnly]:=False) excel.Visible = True ws = CType(wb.Sheets("Sheet Name"), Microsoft.Office.Interop.Excel.Worksheet) ws.Activate() Dim UsedRange As Integer = ws.UsedRange.Rows.Count Dim str As String = Convert.ToChar(64 + "Column number") ws.Range(str + "2:" + str + (UsedRange).ToString).Copy() ws.Range("D1").PasteSpecial() wb.Save() wb.Close() excel.Quit()
You can do even sorting in the code as well… But better to go with the activities