I need to get column index of column with name Attachments as circled in attached image inside a variable?

Learning Hub Academy Feedback
1

Problem is there are 9 other columns with same name and I want index of the first column

image
image1396×105 4.52 KB

2

Plz help its urgent

3

@Gaurav07,

Use VB code to do that :slight_smile:

 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("C:\Input\inputdata.xlsx", [ReadOnly]:=False)
    excel.Visible = True

    ws = CType(wb.Sheets("Summary"), Microsoft.Office.Interop.Excel.Worksheet)
    ws.Activate()
For c As Integer = 1 To CapcUsedColumnsRange - 2
        Dim str As String = Nothing
        If c <= 26 Then
            str = Convert.ToChar(64 + c)
        ElseIf c > 26 And c <= 52 Then
            str = Convert.ToChar(65) + Convert.ToChar(64 + c - 26)
        ElseIf c > 52 Then
            str = Convert.ToChar(66) + Convert.ToChar(64 + c - 52)
        End If

        If CType(ws.Cells(1, c), Microsoft.Office.Interop.Excel.Range).Value.ToString = "Attachments" Then
        system.windows.messagebox.show(c.tostring)
     End If
    Next
4

what changes I need to make in this code for my case and ihave to do this inside invoke code activity right?? what are the arguments for that

5

No arguments required, just change the path and sheet name if your data is in the first row and first column itself

6

my sheet is like this

image
image1416×556 25.9 KB

in which variable I will get the column index??

7

I have to use invoke code activity?? and write this code inside it

8

Yes, use invoke code activity and paste the above code after changing the path and sheet name @Gaurav07 and more thing is inside the IF condition, use break condition to break the for loop

9

in which variable I will be getting the column index

10

C is the variable we initialized for the for loop @Gaurav07

That will give you the index of column and str will give you the column value as BB or AF

11

Getting error like this
Invoke code : Error compiling code\nerror BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Application’. Are you missing an assembly reference? At line 1\nerror BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Workbook’. Are you missing an assembly reference? At line 2\nerror BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Worksheet’. Are you missing an assembly reference? At line 3\nerror BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Application’. Are you missing an assembly reference? At line 4\nerror BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Worksheet’. Are you missing an assembly reference? At line 9\nerror BC30616: Variable ‘c’ hides a variable in an enclosing block. At line 11\nerror BC30451: ‘CapcUsedColumnsRange’ is not declared. It may be inaccessible due to its protection level. At line 11\nerror BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Range’. Are you missing an assembly reference? At line 21\n",
“level”: “Error”,
“timeStamp”: “02:12:08”,
“processVersion”: “1.0.7200.7085”,
“fileName”: “Main”,
“jobId”: “31b9cc21-00b3-4b1a-92a1-3eddc44c92f1”,
“robotName”: “CT”
}

12

You can use Read Range then
Use for each column in DT
column name =Attachment exit on first found
Or
Array columns list and get the index of Column name attachment.

13

can you elaborate this

14

Install Microsoft.Office.Interop.Excel activities

15

index=0

    foreach(DataColumn column in table.Columns)
    {

        Console.WriteLine(row[column],index);

index=index+1
}

16

I am still not getting this can we start from start
I need to find the column index of first column with value- attachment?/

17

Just installing the package I mentioned above will clear the error @Gaurav07

1 Like