Solved How to read or write data from footer of Excel using blueprism?

[adnan]

How can I read or write data from footer of Excel using blueprism?can someone please tell me how to read and then write data from footer of excel file?

 

[VJR]

Hi adnan,

Writing a Code stage to do this would be easy.

- Create a copy of any existing action of the MS Excel VBO
- Remove all unwanted data items, input, output parameters and keep the necessary ones - Refer 1.jpg
- Only pass the handle and the workbook to the Start stage parameter and to the Code stage. You can take a look at the other actions in the VBO.
- The below code is for inserting a Footer.
Note that this is to insert the same Footer in all Sheets. You will need to customize it if you need to add it to specific sheet(s) and if you need different Footers for different sheets.

Try


Dim wb As Object = GetWorkbook(Handle, Source_Workbook)
Dim sheets as Object = wb.Sheets


For Each sheet as Object in sheets
    With sheet.PageSetup             
        .LeftFooter = "This is the Left Footer"
        .CenterFooter = wb.FullName
        .RightFooter = "This is the Right Footer"
   End With   
Next sheet


Success = True

Catch e As Exception
    Success = False
    Message = e.Message
Finally
        

End Try

- Refer 2.jpg for how the three different types of Footers look after running the process.
- Likewise to read a Footer you need to read them in a string and then pass them as an output to a Data item.

With sheet.PageSetup             
   strLeftFooter = .LeftFooter
   strCenterFooter = .CenterFooter
   strRightFooter = .RightFooter
End With

 

[adnan]

hi Vj here are some pics

 

[adnan]

man its saying file too large dont know
jsut be patient and little bit more time please

 

[VJR]

Hi adnan,

Refer the attached file.

 

[adnan]

Hi Vijay,
1. Can you please explain little about this part which is in attachment? these four data items are coming from where or should i just make it as text? please can you explain, I know i am sounding little hectic to you?
2. if you can little elaborate about copy and paste worksheet range because i ve not work on that before?
would be great help for me already you have explained so much in detail.
Regards,
Adnan

 

[VJR]

Hi adnan,

1. When you make a duplicate copy of the already existing tab called ‘Copy and Paste Worksheet Range’, the Data items Handle, Source Workbook, Success and Message will already be there. You only need to create the other three as Text.
It is up to you. Either you first create it and then drag its name in the Start stage properties of ‘InsertFooterInAllSheets’
OR
In the Start stage properties of ‘InsertFooterInAllSheets’, after typing the name click on the small eraser like button in the 'Store In' column which will automatically create those three data items when you click on each of the three buttons.

2. There is nothing needed to be done in the ‘Copy and Paste Worksheet Range’ tab.
As mentioned in the doc you just need to Right click on that tab name at the top and click Duplicate which will ask you for a new name which will be InsertFooterInAllSheets

"Among the several list of action tabs you will find a tab called ‘Copy and Paste Worksheet Range’. Right click on that tab name and click Duplicate. Give a new name as ‘InsertFooterInAllSheets’."

 

[adnan]

Thanks VJ, seriously without your help wont be able to do... I am not giving up therefore will try till end. handle and workbook are understood but how this message and success should be come out? I am new user VJ might be my questions are annoying for you. but explanation can teach me "Copy and paste workbook range" i have found.
1. I have created a handle form create an instance
2. Workbook name form open workbook.

But dont know decision stage what should i write and message comes from exception ?
Please if you can explain this could be great for me

 

[VJR]

Which page and line of the document are you on? Also have you been sequentially following the doc. If not please do so.

 

[VJR]

But dont know decision stage what should i write and message comes from exception ?

You don't need to write anything in that Decision stage.
When you make a duplicate copy of the already existing tab called ‘Copy and Paste Worksheet Range’ then the content of the Decision stage will already be there. Don make any changes in the 'Copy and Paste Worksheet Range’ tab. Simply line by line follow the instructions in the doc and you should be fine.

 

[adnan]

please tell me where i am going wrong?

 

[VJR]

Refer the 'ReadFooterFromFilesInFolder' section of the attached document.

 

[adnan]

Thanks VJR, its great help what you did. you are genius

 

[Manishgarg3005]

Hi I am not able to use this Action properly.Do i need to open microsoft word as Excel.As i could see footers are inserted in word file.

 

[adnan]

Hi Manish,

No you don't need Microsoft word VBO if you are dealing with Excel. if you can write more details where you are having problem then might be it s better.

 

[Manishgarg3005]

Hi I am not able to use this Action properly.Do i need to open microsoft word as Excel.As i could see footers are inserted in word file.

Hi Manish,

No you don't need Microsoft word VBO if you are dealing with Excel. if you can write more details where you are having problem then might be it s better.

Thanks for your help .I am able to achieve this fuctionality.

Do u have any code to paste screenshot in Excel?

 

[adnan]

No I don't know the code but might be you can create a new thread on forum and there will be some people who can guide you. all the best

Regards,
Adnan

 

[VJR]

Hi Manishgarg3005,

You may want to take a look at the below post
http://rpaforum.net/threads/write-images-to-excel.958/post-2751

 

[jleytonn]

Hi adnan,

Refer the attached file.

This is the best solution ever.

Thank you so much @VJR for your explanation.