Get only specific columns from Excel worksheet to Collection using Code stage

[nusratms]

Hi All,

I need to extend the MS Excel VBO action "Get worksheet as collection"to get only specific columns , which can be specified either as comma separated string or collection. Can you please assist me with the code stage for the same?

Regards

 

[slrpa]

I also need answer for this, hope somebody helps

 

[ALALUN]

I think you need to tell about more detail. for example:
What is you input? What is you output?
It is better if you can give the legend to show.

 

[nusratms]

Hi,

The input is an excel sheet. When we read this worksheet as collection, we get all the columns from the sheet. But there is a need to fetch only specific columns into a collection which might either be specified as fields of another collection, or as a single column entry in another collection or a comma separated string.

Regards

 

[jrprajapati]

Hi All,

I need to extend the MS Excel VBO action "Get worksheet as collection"to get only specific columns , which can be specified either as comma separated string or collection. Can you please assist me with the code stage for the same?

Regards

Copy the action "Get worksheet as Collection" action and make new one named "Get specific column as collection"
In code stage copy below code:

Dim ws as Object = GetWorksheet( _
handle, workbookname, worksheetname)
Dim r as Object
r = ws.ListObjects("myTable").ListColumns([Header Name]).Range
r.Select()
r.Copy()

Dim data As String = GetClipboardText()

worksheetCollection = ParseDelimSeparatedVariables( _
data, vbTab, Nothing, Use_Header)

Inputs in start stage of page would be:
Workbook name:
Worksheet Name:
Handle:
Header Name:
Use_Header:

Input for the code stage will be:
Workbook name:
Worksheet Name:
Header Name:
Handle:
Use_Header:

O/P will be ->
Worksheetcollection.

 

[nusratms]

Hi ,

Thank you for your answer !

I have a doubt with the Header Name , can you please let me know how it should be specified in the code ?

I tried using a single column in the Column Names collection with rows having name for every column required as well as specifying the required Column names directly as fields in the Collection Column Names , but I am getting the below Exception:

Internal : Could not execute code stage because exception thrown by code stage: Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))

Regards

 

[jrprajapati]

Hi ,

Thank you for your answer !

I have a doubt with the Header Name , can you please let me know how it should be specified in the code ?

I tried using a single column in the Column Names collection with rows having name for every column required as well as specifying the required Column names directly as fields in the Collection Column Names , but I am getting the below Exception:

Internal : Could not execute code stage because exception thrown by code stage: Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))

Regards

Can you please show the ss of your logic and code stage.

 

[nusratms]

Hi ,

PFA SS for the VBO action and the code stage , I have created the Header_Name input as a Collection , and tried by passing the required columns from the Excel as fields in the Header_Name collection , as well as by using a single column in Header_Name with rows specifying the Columns to be copied from the excel .

Regards

 

[jrprajapati]

Hi ,

PFA SS for the VBO action and the code stage , I have created the Header_Name input as a Collection , and tried by passing the required columns from the Excel as fields in the Header_Name collection , as well as by using a single column in Header_Name with rows specifying the Columns to be copied from the excel .

Regards

Use the [Header Name] as a single column header name instead of collection.
Moreover, in the following line of code replace the myTable with the name of your table in the excel.

r = ws.ListObjects("myTable").ListColumns([Header Name]).Range

Thanks

 

[nusratms]

Hi,

So does that mean "myTable" will be the sheet name , and Header Name will be a column name in the sheet? And the result will be a single column from the excel sheet into the collection?

Regards

 

[jrprajapati]

Hi,

So does that mean "myTable" will be the sheet name , and Header Name will be a column name in the sheet? And the result will be a single column from the excel sheet into the collection?

Regards

No, the table name will be the name of the table in excel. Select table then go to Design Tab and you will be able to see the name of the table on the left top side.

 

[nusratms]

Hi ,

I didn't exactly get how to get the table name , a screenshot for the same would be helpful.

Thank you!!

 

[pradeep surendar]

Hi ,
Can you show what is there in header name input ?