Turn on AutoFilter and Freeze Panes with Visual Basic

[RickMartin]

Would anyone have the correct code to turn on AutoFilter or Freeze Panes with VB using Microsoft.Office.Interop.Excel. below is a sample ,but, some of the lines are incorrect. Dim excelApplication As New Application Dim excelWrkBook As Workbook Dim excelWrkSheet as Worksheet Dim excelRange as Range Try excelApplication.DisplayAlerts = False excelWrkBook = excelApplication.Workbooks.Open("C:\TestFolder\TestFile.xlsx") excelRange = excelWrksheet.Range("TestFile") excelRange.AutoFilter() excelWrkBook.Save excelWrkBook.Close() excelApplication.Quit() thank you

[RickMartin]

i was lucky to get the code from a developer, here is the code that works. Dim wexcel As New Application Dim wBook As Workbook Dim wSheet As Worksheet ' Dim excelRange As Range Dim excelApplication As New Application ' Dim excelWrkBook As Workbook wexcel.Visible = True 'open your file wexcel.Workbooks.Open(""C:\TestFolder\TLBUpdatedSpreadsheet.xlsx"") 'select the first workbook into your global variable wBook = wexcel.Workbooks(1) 'select the first sheet into your global variable wSheet = wBook.Sheets(1) wSheet.Activate() 'select your column wSheet.Columns(""D"").Select() 'freeze your selection wSheet.Application.ActiveWindow.FreezePanes = True 'choose the range of columns you want to autofilter wSheet.Range(""A:AZ"").AutoFilter(Field:=1, Operator:=XlAutoFilterOperator.xlFilterValues) 'save your work as a new file wBook.SaveAs(""C:\TestFolder\New_TLBUpdatedSpreadsheet.xlsx"") wBook.Close() wexcel.Application.Quit()

[Ravikiran44]

Hi Rick,

Could you please provide me the code to freeze a particular row in excel using code stage.

Thanks

------------------------------
Ravi kiran P
------------------------------

[devneetmohanty07]

Hi Ravi,

For your desired use case please follow the below steps to create a new action for performing the required operation.

NOTE: You should ideally create a duplicate business object for the same and do the required modifications so that the original business object is not affected and you can use it for reference.

Create a new action called 'Freeze Panes' within the 'MS Excel' VBO object which consists of other actions. Add the following input parameters to this action:

- Handle (Number) : The instance of the excel sessions held in the dictionary to establish the current excel connection.

- Workbook (Text) : The workbook name returned while using either 'Open Workbook' or 'Create Workbook' actions.

- Worksheet (Text) : The worksheet name where the operation needs to be performed.

- Cell Reference : The cell address from where the freeze pane action needs to be performed.

Solution Workflow:



Add the code stage named 'Freeze Pane' with the following parameters and code:



Code:



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

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

wb.Activate()
ws.Activate()

excel = ws.Application
sheet = excel.ActiveSheet
range = sheet.Range(Reference)

range.Rows(1).Select()
excel.ActiveWindow.FreezePanes = True

Test Scenario:

I have created a workflow and provide the parameters for 'Freeze Pane' action as follows in the process studio:



NOTE: Ensure that all the actions are used from the same VBO where Freeze Pane action has been created in order to avoid unexpected errors.

The input file before execution looks like below:



Upon executing the workflow, the results are as follows:



Here, the freeze pane has been added at the given cell reference. Also, please find the attached sample business object for your reference.

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------