I have excel sheet where I have two column of same name…
While I use read range, it shows the error that the columnname belongs to this datable,
Please help me in resolving the issue
Yup…
yah if we have two column names similar we would get this error
better uncheck the ADD HEADERS property and try
but to refer each column we need to column index only where 0 is the column index for first column
Cheers @Sweety_Girl
Yes… But we can you the column index, if the column name is in the same order.
In other case?
Any other way?
If the column order changes? we will not be able to process correct!
yes correct
but we got to go for index if the columnnames are same buddy
and make sure that they are in order
Cheers @Sweety_Girl
Anyway that we can rename the duplicate column?
- Using VB code, you can loop through all the column names
- Then add the columns to array or collection while iterating
- Before adding the value to the collection, check the array or collection contains the same value
- If exists, we will have the index , so change the name of the column
This can be done using the VB code in the InvokeCode activity @Sweety_Girl
Let me know if you want code for it
How to change the name of the duplicate column?
Any link for VB code
You can do it on your own as we have lot of threads available online. But here is the code… @Sweety_Girl
Make sure you have installed Microsoft.Office.Interop.Excel package
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(File full path, [ReadOnly]:=False)
excel.Visible = True
ws = CType(wb.Sheets(Sheet name), Microsoft.Office.Interop.Excel.Worksheet)
ws.Activate()
Dim MTUsedColumnsRange As Integer = ws.UsedRange.Columns.Count
Dim arrayColNames(MTUsedColumnsRange) As String
For c As Integer = 1 To MTUsedColumnsRange
Dim colName As String = CType(ws.Cells(1, c), Microsoft.Office.Interop.Excel.Range).Value.ToString
If arrayColNames.Contains(colName) Then
colName = colName + "2"
CType(ws.Cells(1, c), Microsoft.Office.Interop.Excel.Range).Value = colName
End If
arrayColNames(c) = colName
Next
wb.Save()
wb.Close()
excel.Quit()
Thank you, I will work it and ping you about it
I have nearly 40+ columns in it
In that they have only 2 column are check the 2column name in if
Here we have like 40+ column, can we apply condition for 40 column in single if