cancel
Showing results for 
Search instead for 
Did you mean: 

Freeze panes

Ravikiran44
Level 3
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
------------------------------
2 REPLIES 2

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:

32043.png

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

32044.png
Code:

32045.png
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:

32046.png
32047.png

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:

32048.png

Upon executing the workflow, the results are as follows:

32049.png
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

----------------------------------
------------------------------
---------------------------------------------------------------------------------------------------------------------------------------
Hope this helps you out and if so, please mark the current thread as the 'Answer', so others can refer to the same for reference in future.
Regards,
Devneet Mohanty,
SS&C Blueprism Community MVP 2024,
Automation Architect,
Wonderbotz India Pvt. Ltd.

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

----------------------------------
------------------------------
---------------------------------------------------------------------------------------------------------------------------------------
Hope this helps you out and if so, please mark the current thread as the 'Answer', so others can refer to the same for reference in future.
Regards,
Devneet Mohanty,
SS&C Blueprism Community MVP 2024,
Automation Architect,
Wonderbotz India Pvt. Ltd.