Freeze panes

[Ravi_kiranP]

Hi ,  I want to freeze panes for a particular row in excel,
Could any one have the code to achieve that.

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.

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

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

[devneetmohanty07]

Hi Ravi,

I am attaching the code file here as I am not able to attach the same while editing my posts.

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

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