Excel Read Rangee

Help
9

Anyway that we can rename the duplicate column?

10
  1. Using VB code, you can loop through all the column names
  2. Then add the columns to array or collection while iterating
  3. Before adding the value to the collection, check the array or collection contains the same value
  4. 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

11

How to change the name of the duplicate column?

1 Like
12

When the value exists in the array, add a specific value or any thing to the name @Sweety_Girl

13

Any link for VB code

14

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()
2 Likes
15

Thank you, I will work it and ping you about it

16

Capturedatsss
@HareeshMR This error

17

kindly have a view on this

Cheers @Sweety_Girl

18

I have nearly 40+ columns in it

1 Like
19

so were we facing any issue due to that
Cheers @Sweety_Girl

20

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

1 Like
21

let me check once
few mins pls buddy
Cheers @Sweety_Girl

22

Sure

23

Do these steps to avoid that @Sweety_Girl

  1. Open a blank workbook in Excel.
  2. Go to File > Options > Trust Center
  3. Click on Trust Center Settings…
  4. Under Macro Settings , make sure Trust access to the VBA project object model is checked.
24

Okay done…
Capturejjj

25

Can you post the entire error you are getting?

Have you changed the file path and the sheet name? @Sweety_Girl

26

thanks buddy its working

27

Hi @Sweety_Girl

Apart from other solutions guys have given, This is another way of doing it from workflow itself…

Check out the sample attached…
Excel with same column name
image

The result
image

Here’s the code for it :slight_smile:
I have added some comments as well for you to understand… let know if it’s not clear…
TestData.xlsx (8.8 KB)
ColumnNameCheck.xaml (14.0 KB)

Hope it helps.

3 Likes
28

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.