topic Anyone know how the MS Excel VBO - Extended::Remove Duplicates object works? in Product Forum

Anyone know how the MS Excel VBO - Extended::Remove Duplicates object works?

JeremyRTDean — Thu, 11 Jan 2024 23:01:12 GMT

All I get is: "The parameter is incorrect" (see attachment)

handle: [Boris]

Workbook name: [Duplicates File]

Worksheet name: "Sheet1"

Range: "A1:C10"

Column Indexes: "0,1,2"

Re: Anyone know how the MS Excel VBO - Extended::Remove Duplicates object works?

PvD_SE — Mon, 15 Jan 2024 12:05:12 GMT

Hi Jeremy,

We don't seem to have that action in that object, so I cannot test this situation for you.

But, usually you get better info on what is going wrong if you step into the object and check the data items there. Also, you may get error data in the object that doesn't make it back to where your process called the object.

Happy coding!
Paul
Sweden

Re: Anyone know how the MS Excel VBO - Extended::Remove Duplicates object works?

MichealCharron — Mon, 15 Jan 2024 13:40:25 GMT

@Jeremy Dean

I know that Excel's array indexing is 1-based so you could try "1,2,3" and see if that works for you.

Re: Anyone know how the MS Excel VBO - Extended::Remove Duplicates object works?

JeremyRTDean — Mon, 15 Jan 2024 19:22:26 GMT

I had tried "1,2,3" and a few other variations to no avail. Rather than try to unpick the object I used 'SPGMI Collection Manipulation Booster' from the Digital Exchange. 'Remove Duplicate Rows' from a Collection worked perfectly.

Re: Anyone know how the MS Excel VBO - Extended::Remove Duplicates object works?

Anton__Hosang — Wed, 24 Jan 2024 21:32:23 GMT

I remember having to deal with this shortcoming.

A rare case of the Excel Object model code not being implemented correctly outside of VBA - the VBA equivalent works fine.

The array is not being passed properly as a parameter, so we have to create it on the fly by transforming into an Array from a List (other other suitable Enumerable)

columnIndices is a text that has the comma-separated columns that determine duplicate status, e.g. "3", "1,2"

Dim ws As Object = GetWorksheet(handle, workbookname, worksheetname)

Dim r As Object = ws.Range(cellref)

Dim cr As Object 'Range --> CurrentRegion

If r.Cells.CountLarge > 1 Then

cr = r

Else

cr = r.CurrentRegion

End If

success = False

message = ""

Try

Dim columnIndicesLst As List(Of Object) = New List(Of Object)

For Each index In columnIndices.Split(","c)

columnIndicesLst.Add(Trim(index))

Next index

'After building a List of the columns (as Objects as per the spec), we transform to the necessary array

cr.RemoveDuplicates(columnIndicesLst.ToArray(), IIf(isHeader, 1, 2))

success = True

Catch ex As Exception

success = False

message = ex.ToString()

End Try