topic RE: Hi Ahmed Soltan, I don't… in Product Forum

Remove Duplication in Excel

Anonymous — Sat, 09 Feb 2019 19:35:00 GMT

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

Hi Ahmed Soltan, I don't…

BastiaanBezemer — Thu, 14 Feb 2019 01:00:00 GMT

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

RE: Hi Ahmed Soltan, I don't…

SwaroopM — Thu, 23 Jan 2020 19:00:00 GMT

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
------------------------------

RE: Hi Ahmed Soltan, I don't…

AyshaM — Tue, 11 Aug 2020 15:10:00 GMT

Were you able to find a solution for this?

I have got the same type mismatch error.

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