Excel Automation - Add Comment

[kiss4344]

Hi - I want to add comment to a particular Cell in excel sheet, How to proceed with this after Opening excel worksheet sheet in BluePrism?

 

[Sachin_Kharmale]

Hi,

Add new action "Add Comment to Cell" in Blue Prism "MS Excel VBO"

1) Add Code Stage to Action
View attachment 1560331827319.png
2) Input will be 1-Handle 2- CellRef 3-Comment
View attachment 1560331861989.png
3)Code Stage Code will be
View attachment 1560331889876.png

Add single line in code stage
GetWorkbook(handle, "").ActiveSheet.Range(cellref,cellref).AddCommentThreaded (Comment)

Call Add Comment to Cell from Process

1)Create Instance
2)Open Workbook
3)Add Comment to Cell
4)Close Instance

Then you will get output like bellow
View attachment 1560337172163.png

i hope it will help you.

Best,
Sachin

 

[kiss4344]

Sachin - I've tried the same but in code stage getting compile error, could you please help me to resolve

Can you give us the function code for AddCommentThreaded to add in Global Code

"The name GetWorkbook does not exist in the current context"View attachment compile error.JPG

 

[Sachin_Kharmale]

Hi ,
Just add new page above the Code stage and select Existing Activate Workbook Page From Blue Prism MS Excel VBO.

and Give Input parameter handle and workbook Name As ""

Please refer bellow screenshot

View attachment 1560407899460.png

Best,
Sachin

 

[kiss4344]

Sachin - Finally while running the process I am getting error - The given key was not present in the dictionary, attached screenshot belowView attachment Error in Process.JPG

 

[Sachin_Kharmale]

Please call all actions for your flow from same object

Possibly causes to above Error - if you have 2 MS Excel Object in Blue Prism
1- Object 1 - MS Excel VBO
2- Object 2- MS Excel VBO Extended

if You have created new action in MS Excel VBO Extended and created new instance with Object 1 output will handle
and passed this handle to object 2
so object will be different so make sure all actions called from same object .

If you added action in Object 2 then call all actions like Create Instance , Open Workbook , Add Comment to cell , Close Instance from same.

Best,
Sachin

 

[kiss4344]

Sachin - That issue has been resolved and now I am getting new error, can you check with the screenshot
Internal : Could not execute code stage because exception thrown by code stage: Exception from HRESULT: 0x800A03EC
View attachment NewError.JPG

 

[Sachin_Kharmale]

If You want to add a comment to Cell A3 Then Passed Input to action "A3"

View attachment 1560421693563.png

I Hope it will solve your issue.
Best,
Sachin

 

[kiss4344]

If You want to add a comment to Cell A3 Then Passed Input to action "A3"

View attachment 4050

I Hope it will solve your issue.
Best,
Sachin

Still getting same error Sachin, that is not resolving our problem.

 

[kiss4344]

Internal : Could not execute code stage because exception thrown by code stage: Exception from HRESULT: 0x800A03EC

 

[kiss4344]

Please check my flow and correct me if I am wrong


View attachment ProcessMainPage.JPG

 

[Sachin_Kharmale]

Yes you can add your comment as your requirement I Hope all your issue resolved now ?

 

[Sachin_Kharmale]

Internal : Could not execute code stage because exception thrown by code stage: Exception from HRESULT: 0x800A03EC

Root Cause for above mentioned problem is .
When you apply given solution it will work fine.
Ex- Comment - Good Morning
Cell Number - A10
You ran the code it will run successfully and Comment will added to the Cell Number A10
But When you are changing the comment From Good Morning any other Text and Ran code then it will throw above error message.

So fix your comment and apply if you want change the comment or replace the existing comment to cell then we need to apply another solution.

 

[kiss4344]

Actually I want to change the comment, I mean need to append my new comment to the existing comment. Please let me know the other solution if possible. @Sachin_Kharmale

 

[Sachin_Kharmale]

Hi Kiss,
If you want append new comment to existing comment

Copy the bellow code and paste in your Existing code stage.

Dim str As String
Dim FinalComment As String
str=GetWorkbook(handle, "").ActiveSheet.Range(cellref,cellref).Comment.text
GetWorkbook(handle, "").ActiveSheet.Range(cellref,cellref).Comment.Delete
FinalComment= str & Comment
GetWorkbook(handle, "").ActiveSheet.Range(cellref,cellref).AddComment(FinalComment)


Note :- Delete your Existing code from your code stage and do not change input and output Parameter

I hope it will help you.

 

[kiss4344]

Hi Kiss,
If you want append new comment to existing comment

Copy the bellow code and paste in your Existing code stage.

Dim str As String
Dim FinalComment As String
str=GetWorkbook(handle, "").ActiveSheet.Range(cellref,cellref).Comment.text
GetWorkbook(handle, "").ActiveSheet.Range(cellref,cellref).Comment.Delete
FinalComment= str & Comment
GetWorkbook(handle, "").ActiveSheet.Range(cellref,cellref).AddComment(FinalComment)


Note :- Delete your Existing code from your code stage and do not change input and output Parameter

I hope it will help you.

This works perfect for appending comment, thanks again for your prompt help.

 

[kiss4344]

Hi Kiss,
If you want append new comment to existing comment

Copy the bellow code and paste in your Existing code stage.

Dim str As String
Dim FinalComment As String
str=GetWorkbook(handle, "").ActiveSheet.Range(cellref,cellref).Comment.text
GetWorkbook(handle, "").ActiveSheet.Range(cellref,cellref).Comment.Delete
FinalComment= str & Comment
GetWorkbook(handle, "").ActiveSheet.Range(cellref,cellref).AddComment(FinalComment)


Note :- Delete your Existing code from your code stage and do not change input and output Parameter

I hope it will help you.

This works perfect for appending comment, thanks again for your prompt help

Hi Kiss,
If you want append new comment to existing comment

Copy the bellow code and paste in your Existing code stage.

Dim str As String
Dim FinalComment As String
str=GetWorkbook(handle, "").ActiveSheet.Range(cellref,cellref).Comment.text
GetWorkbook(handle, "").ActiveSheet.Range(cellref,cellref).Comment.Delete
FinalComment= str & Comment
GetWorkbook(handle, "").ActiveSheet.Range(cellref,cellref).AddComment(FinalComment)


Note :- Delete your Existing code from your code stage and do not change input and output Parameter

I hope it will help you.

@Sachin_Kharmale

I need to edit this code change to check whether comment is already there or not.

If there is no comment then we can add comment and msg, if there is comment then need to append.