cancel
Showing results for 
Search instead for 
Did you mean: 

Remove Duplication in Excel

Anonymous
Not applicable
hi ,  i want to do in blue prism as the Excel do in Remove Duplicate features so i created the below  code but i have some error can anyone help me ? the errors  Description: Compiler error at line 35: 'xlYes' is not declared. It may be inaccessible due to its protection level. Description: Compiler error at line 35: 'Array' is a type and cannot be used as an expression. Description: Compiler error at line 35: Method arguments must be enclosed in parentheses.         the Code Dim sw, dw As Object Dim ss, ds As Object Dim excel, sheet, varUsedRange As Object Dim FilteredCount as Long Try sw = GetWorkbook(Handle, Source_Workbook) ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet) sw.Activate() ss.Activate() excel = ss.Application sheet = excel.ActiveSheet varUsedRange = sheet.UsedRange().address   'you can give your own Range If sheet.AutoFilterMode Then     sheet.AutoFilterMode = False    'Turn off filters if already applied End If     sheet.Range("A1:C100").RemoveDuplicates Columns:=Array(1,2), Header:=xlYes Success = True Catch e As Exception     Success = False     Message = e.Message Finally     sw = Nothing     ss = Nothing     dw = Nothing     ds = Nothing     excel = Nothing     sheet = Nothing     varUsedRange = Nothing End Try
3 REPLIES 3

BastiaanBezemer
Level 5
Hi Ahmed Soltan, I don't give you the full solution, but rather some hints. You are adapting VBA code to work in VB.net, which is indeed a good way when trying to extend the Excel VBO functionality. Excel VBA itself knows that has xlYes a value of 1 (And xlNo a value of 2), BluePrism does not know this. Hence you need to declare and assign xlYes. In Excel VBA, indeed an array can be done with Array(), in VB.net you just put it between curly brackets. Hope this helps you in the right direction. Don't hesitate to write again if still stuck. -------------------------------------------------------------------------------------------------------- Dim sw As Object = nothing dim dw As Object = nothing Dim ss As Object = nothing  dim ds As Object = nothing Dim excel As Object = nothing dim sheet As Object = nothing dim varUsedRange As Object = nothing Dim FilteredCount as Long = 0dim xlYes as integer = 1 Try     sw = GetWorkbook(Handle, Source_Workbook)     ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)     sw.Activate()     ss.Activate()     excel = ss.Application     sheet = excel.ActiveSheet     varUsedRange = sheet.UsedRange().address   'you can give your own Range     If sheet.AutoFilterMode Then         sheet.AutoFilterMode = False    'Turn off filters if already applied     End If             sheet.Range(""A1:C100"").RemoveDuplicates(Columns:={1,2}, Header:=xlYes)     Success = True Catch e As Exception     Success = False     Message = e.Message Finally     sw = Nothing     ss = Nothing     dw = Nothing     ds = Nothing     excel = Nothing     sheet = Nothing     varUsedRange = Nothing End Try

Hi Bastiaan,

Greetings!

I had tried the code which you mentioned above. When I ran I am getting an error saying Type Mismatch. Any suggestions. 

I tried another method of initialising Columns as an Objects of Array. Even that failed as well. Please in case you have any solution let me know.

Thanks in advance 🙂

------------------------------
Swaroop M
Consultant
EY
Asia/Kolkata
------------------------------

Were you able to find a solution for this?

I have got the same type mismatch error.



------------------------------
Aysha M
------------------------------