Excel Tabs

[RineshSingh]

Hi All, 

I am working with multiple tabs in excel and the requirement is to change the tab to a certain color. Eg. If a spreadsheet contains a specific keyword than the tab color should be changed to "Red". Does Blue Prism have functionality to change the tab color on an excel spreadsheet? 

thank you

[devneetmohanty07 · Accepted solution]

Hi Rinesh,

Currently, I don't think there is any action to achieve this functionality in the current 'MS Excel' or 'MS Excel - Extended' VBO perhaps someone can confirm on the same as well.

But you can achieve this functionality by extending either 'MS Excel' or 'MS Excel - Extended' VBO by creating a new action within the object.

NOTE: I would recommend create a duplicate object of these objects and make your changes there so that you always have a backup with you in case anything goes wrong.

Now, you need to create a new action in your already existing Excel VBO business object named 'Change Worksheet Tab Color' and provide the following input parameters to your action:

Handle : This is a number type data item which will hold the current session dictionary value.

Workbook : This is a text type data item where you need to pass the workbook name that you might get from 'Create Workbook' or 'Open Workbook' action.

Worksheet: This is a text type data item which indicates the name of the worksheet in your excel workbook file whose tab color you want to change.

R : This is a number type data item which can consists of value ranging between 0 and 255 in order to determine the red color pixel in your RGB color combination.

G : This is a number type data item which can consists of value ranging between 0 and 255 in order to determine the green color pixel in your RGB color combination.

B : This is a number type data item which can consists of value ranging between 0 and 255 in order to determine the blue color pixel in your RGB color combination.



Now, you can add a code stage named 'Change Worksheet Tab Color' and add the following inputs parameter and map the data items to it:


Code:

Dim ws, excel, sheet As Object

ws = GetWorksheet(Handle, Workbook, Worksheet)

ws.Activate()

excel = ws.Application
sheet = excel.ActiveSheet

sheet.Tab.Color = RGB(R, G, B)​

You should have your workflow ready as shown below:




Now, publish your action and then use all the actions of the same business object where you have done the changes in your process studio while interacting with the excel file in order to avoid any exceptions. I have a sample process studio workflow shown below:



Here, I am creating an excel instance, then opening my excel file, then making it visible on the screen and then I use my new created action which has the below parameters:



So here since my R value is 255 which is maximum while G and B values are 0 hence, the resultant color would be red since R is dominant and has maximum value which means that tab color of my 'Parameters' sheet should be Red.

Now, before running this action my file looks like this:



After executing the action, you can observe below that the color of the sheet tab got converted to Red:



So just by manipulating with the R, G and B input parameters in this action you can change any specific worksheet tab to any color you want.


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

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

[RineshSingh]

Hi Devneet,

Firstly, thank you for taking the time to look into this functionality in such detail - its appreciated. 
I have also looked at the existing functionality available in the Standard Excel and the extended version and was not able to find tab color related functions. 

I will definitely give the below an attempt and feedback here on the results. 

Thanks once again

[devneetmohanty07]

Hi @Rinesh Singh

Just checking if you were you able to resolve your issue?​

[RineshSingh]

Thanks Devneet, this solution worked perfectly.