Remove Duplication in Excel

Anonymous
Not applicable
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
09-02-19 07:35 PM
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
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
14-02-19 01:00 AM
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
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
23-01-20 07:00 PM
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
------------------------------
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
------------------------------
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
11-08-20 04:10 PM
Were you able to find a solution for this?
I have got the same type mismatch error.
------------------------------
Aysha M
------------------------------
