How to Remove Excel Filter via Blueprism RPA

jassi_123

Member
Hello Team,

I have a excel sheet which sometime contains filter and sometime not. So I wanted to check via RPA if any filter exists on the Excel Sheet then I wanted to remove that filter from the excel sheet and if the sheet doesn't contains any filter then it can move to the next stage.

Can someone please tell me the best way to achieve this objective.

Regards,
(Jaspreet Singh)
 

sivagelli

Well-Known Member
So I wanted to check via RPA if any filter exists on the Excel Sheet then I wanted to remove that filter from the excel sheet and if the sheet doesn't contains any filter then it can move to the next stage.
Do you mean to remove any filter applied on the columns ? or Remove the filter it-self on the sheet?
 
You can write a code stage to enhance the Excel VBO:
Pass in 3 inputs such as xlHandle, wbName, & wsName(Untested):
Code:
    Dim ws As Object
    ws = GetWorkbook(xlHandle, wbName).Worksheets(wsName)
    
    Dim table As Object
    For Each table In ws.ListObjects
        If table.ShowAutoFilter Then
            table.ShowAutoFilter = False
        End If
    Next table
    
    If ws.AutoFilterMode Then
        ws.AutoFilterMode = False
    End If
 

sivagelli

Well-Known Member
The below code would remove Auto Filters on the sheet. Note, that this will not remove the filters on Tables if your sheet has tables.

In your Excel VBO object, add a new page and name it as "Turn Off AutoFilter"

Add a Code stage on to the page with
  • Input parameters: Handle, Workbook, Worksheet
  • Output Parameters: Success, Message
  • Add the below code in the code tab

Code:
Dim wb, ws As Object
Dim excel, sheet, range As Object

Try

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet

sheet.AutoFilterMode= False

Success = True

Catch e As Exception
    Success = False
    Message = e.Message
Finally
    wb = Nothing
    ws = Nothing
    excel = Nothing
    sheet = Nothing
    range = Nothing
End Try

The Start Stage on the page should accept the parameters: Handle, Workbook, Worksheet
The End Stage on the page should send the parameters: Success, Message

Publish this page and Save.

Now, you are good to use the above action 'Turn Off AutoFilter'.

Flow would be:
1. Start
2. Create Instance
3. Open Workbook
4. Activate Workbook
5. Turn Off AutoFilter and pass the inputs- handle, workbook name and worksheet on which you want to remove filters
6. Close workbook with Save set to True
7. Close Instance with Save set to True
8. End


Post back how it goes!
 
Hi
i am trying this setup, but it states compile errors like te attached, how can i solve those?
Many thanks for your help :) Ralph
 

Attachments

  • Compile error.JPG
    45.3 KB · Views: 79
Top