Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Clear Duplicate Values from Excel sheet VB code stage

Go to answer
BENDRES
Level 3

‎07-11-24 12:42 PM

Hi Everyone,

Please assist me in getting the VB code for Blue Prism to clear duplicate values from an Excel sheet.

For reference, kindly refer to the attached image.Capture.PNG

2 BEST ANSWERS

Helpful Answers

Go to answer
Mohamad_747
MVP
In response to BENDRES

‎10-11-24 08:55 AM - edited ‎10-11-24 09:00 AM

Hi @BENDRES 

Follow these steps : 

Go to MS EXCEL VBO and add a new action, name it 'Apply Remove Duplicate [CUSTOM]'. Then drag and drop a code stage like this : 

Mohamad_747_0-1731228315777.png

Now open the code stage and provide these inputs : 

Mohamad_747_1-1731228391292.png

Do the same for output : 

Mohamad_747_2-1731228422234.png

in code stage put this : 

Dim ws as Object
Dim previousVAL As String
Dim currentVAL As String
 
ws = GetWorksheet(handle, workbookname, worksheetname)
 
Try
previousVAL = ""
 
For i = start_row To last_row
 
currentVAL = ws.Cells(i, remove_duplicate_column).Value
     
        If currentVAL = previousVAL Then
            
            ws.Cells(i, remove_duplicate_column).Value = ""
        Else
           
            previousVAL = currentVAL
        End If
 
Next i
 
Catch ex As Exception
exceptionMessage = ex.Message
End Try

 

Like this :

Mohamad_747_8-1731229051426.png

 

Now, Your action should look like this : 

Mohamad_747_3-1731228500748.png

 

Publish your action, save and go to process now.

Your process should look like this : 

Mohamad_747_4-1731228548559.png

Lets do the test now. 

I have this excel file see below : 

Mohamad_747_5-1731228648948.png

I want to make the remove duplicate on my column A so its my first column.

I want also to start at cell 2 and finish at cell 8 

so i give these parameters in my new action see below : 

Mohamad_747_6-1731228687221.png

now run your flow and you will have this : 

Mohamad_747_7-1731228866126.png

Try it and let me know. If it works for you, please mark me as best answer.

 

 

View answer in original post

Go to answer
Mohamad_747
MVP

‎10-11-24 06:53 PM

@BENDRES 

By the way you can use the latest version of MS EXCEL VBO in DX : https://digitalexchange.blueprism.com/dx/entry/3439/solution/ms-excel-vbo

you can find this action : 

Mohamad_747_0-1731264784274.png

Try it and let us know if it work for you mark me as best answer

View answer in original post

7 REPLIES 7

Go to answer
Mohamad_747
MVP

‎07-11-24 07:42 PM - edited ‎07-11-24 07:43 PM

Hi @BENDRES 

What i can suggest you its to get your excel file as a collection then you make the remove duplicate and then you save your excel file.

It will be easier.

 

Is it ok for you ? or its mendatory to have a custom code stage ?

0 Likes

Go to answer
BENDRES
Level 3
In response to Mohamad_747

‎08-11-24 07:22 AM - edited ‎08-11-24 07:26 AM

Hi @Mohamad_747

I appreciate your suggestion.

Since I have a lot of data in 25 columns, I don't want to take it into collection because it could become stuck or there might be a format change.

It will work better with coustom code because just need clear the cell value.

0 Likes

Go to answer
Mohamad_747
MVP
In response to BENDRES

‎10-11-24 08:55 AM - edited ‎10-11-24 09:00 AM

Hi @BENDRES 

Follow these steps : 

Go to MS EXCEL VBO and add a new action, name it 'Apply Remove Duplicate [CUSTOM]'. Then drag and drop a code stage like this : 

Mohamad_747_0-1731228315777.png

Now open the code stage and provide these inputs : 

Mohamad_747_1-1731228391292.png

Do the same for output : 

Mohamad_747_2-1731228422234.png

in code stage put this : 

Dim ws as Object
Dim previousVAL As String
Dim currentVAL As String
 
ws = GetWorksheet(handle, workbookname, worksheetname)
 
Try
previousVAL = ""
 
For i = start_row To last_row
 
currentVAL = ws.Cells(i, remove_duplicate_column).Value
     
        If currentVAL = previousVAL Then
            
            ws.Cells(i, remove_duplicate_column).Value = ""
        Else
           
            previousVAL = currentVAL
        End If
 
Next i
 
Catch ex As Exception
exceptionMessage = ex.Message
End Try

 

Like this :

Mohamad_747_8-1731229051426.png

 

Now, Your action should look like this : 

Mohamad_747_3-1731228500748.png

 

Publish your action, save and go to process now.

Your process should look like this : 

Mohamad_747_4-1731228548559.png

Lets do the test now. 

I have this excel file see below : 

Mohamad_747_5-1731228648948.png

I want to make the remove duplicate on my column A so its my first column.

I want also to start at cell 2 and finish at cell 8 

so i give these parameters in my new action see below : 

Mohamad_747_6-1731228687221.png

now run your flow and you will have this : 

Mohamad_747_7-1731228866126.png

Try it and let me know. If it works for you, please mark me as best answer.

 

 

Go to answer
BENDRES
Level 3
In response to Mohamad_747

‎10-11-24 02:28 PM - edited ‎10-11-24 02:35 PM

Hi @Mohamad_747,

I wanted to extend my sincere thanks for sharing the Blue Prism VB code to clear duplicate values from an Excel sheet. I tested it & it worked perfectly, meeting my requirement as expected.

Can we add sorting of sheet with Column "A" before clearing the duplicate cell.

Your support and guidance have been invaluable & this solution will greatly enhance my automation process.

Thanks once again for your help!

Best regards

0 Likes

Go to answer
Mohamad_747
MVP
In response to BENDRES

‎10-11-24 04:46 PM - edited ‎10-11-24 04:48 PM

Hi @BENDRES 

Sorting on which criteria ascending or descending ?

What if we have blank value ?

Please mark me as best answer.

0 Likes

Go to answer
Mohamad_747
MVP

‎10-11-24 06:53 PM

@BENDRES 

By the way you can use the latest version of MS EXCEL VBO in DX : https://digitalexchange.blueprism.com/dx/entry/3439/solution/ms-excel-vbo

you can find this action : 

Mohamad_747_0-1731264784274.png

Try it and let us know if it work for you mark me as best answer

Go to answer
BENDRES
Level 3

‎11-11-24 05:27 AM

Hi @Mohamad_747 ,

It meets my requirements and performs as expected.

Again, thank you for your assistance!

Best regards