Cell Colouring and Saving Data From Excel to Paint in BP?

Shweta

Active Member
Hi Everyone,

I have some query regarding the Excel operations:

1. I need to highlight the column with Red, (say, from C1:C3 or G1:G5 , should be highlighted with Red). So, can anyone please suggest me, on how can I achieve this?
2. How can I copy entire data from excel and paste in paint and then save the image, in a particular folder?

Thanks!
 

VJR

Well-Known Member

Shweta

Active Member
I need to paste entire data of Excel into paint (or taking screenshot of entire data) and then send it into email body.
 

VJR

Well-Known Member
Is the Excel data visible on the screen Or are there many rows and you have to scroll down?
Does it come in one screenshot?
 

Shweta

Active Member
It depends on data.. Sometimes, it may come in one screenshot, but most of time, there will be a huge data only, so that may not come in one screenshot.
 

VJR

Well-Known Member
Yes, Excel data is visible on screen.
In that case again you can search in the forum for the post on capturing screenshot based on co-ordinates. This post also has the option to save it as an Image file.
 

VJR

Well-Known Member
It depends on data.. Sometimes, it may come in one screenshot, but most of time, there will be a huge data only, so that may not come in one screenshot.
Were you able to achieve this in the case the data is not visible on the screen? Let me know if you designed a solution for this or if you are still looking for it.
 

Shweta

Active Member
Hi VJ,

I am still looking for a solution.

Using macro, I am able to copy that screenshot in another sheet (within same excel), but I want that to paste on desktop or some other path in .png format.

The macro that I used is:

Sub Screenshot()
Range("A1:L5").Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
Sheet("Sheet2").Select
ActiveSheet.Pictures.Paste.Select
End Sub


Thanks!
 

VJR

Well-Known Member
Hi Shweta,

I have added a few lines to your code.

Code:
Sub Screenshot()
Range("A1:D5").Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
Sheets("Sheet2").Select
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
Set chtTemp = ActiveChart
chtTemp.Paste
chtTemp.Export Filename:="C:\test\temp.png"
End Sub
 

Shweta

Active Member
Hi VJ,

I tried this code and it worked also.

However, the image quality is not good and some fields are not clearly visible.

Thanks!
 

VJR

Well-Known Member
Hi VJ,

I tried this code and it worked also.

However, the image quality is not good and some fields are not clearly visible.

Thanks!
That is exactly what happens when it is exported as a Chart object from Excel.
As time permits I will try to put a way of exporting an excel range to an image file from Blue Prism itself.
 

VJR

Well-Known Member
Hi Shweta,

This will require a code stage and you can use this object for any other purpose too that requires saving an image from a Clipboard to an Image file.

- Create a new action in the 'Utility Image Manipulation' VBO. (Rick click on any of the tabs on the top and click 'New')
You can even make a duplicate copy of any of the existing actions in this VBO and then work on it. That will be easier since you will have a ready made Object diagram that looks similar like the one shown below.

- The Object diagram looks as follows:
View attachment 1539157565815.png

- Start stage properties
View attachment 1539157597967.png

- End stage properties
View attachment 1539157636013.png

- Code stage Input parameters
View attachment 1539157661109.png

- Code stage Output parameters
View attachment 1539157682714.png

- Code stage
View attachment 1539157849364.png

Below is the code to be pasted in the Code stage
Code:
Dim im As Image

Try

im = Clipboard.GetImage
FullSavedScreenshotPath = OutputFolderPath & Format(now, "dd-MMM-yy hh-mm-ss") & ".jpeg"
im.Save(FullSavedScreenshotPath)

Success = True

Catch e As Exception
    Success = False
    Message = e.Message
Finally
    im = nothing
End Try


- Save and Publish this action

- Go to the Process and have a process diagram similar to the below
View attachment 1539157984288.png

- In the Select simply give the Cell Reference of the data that needs to be copied eg; "A1:Z20"

- Copy is to copy the above selected Range to the Clipboard

- The new action that you just created under Image Manipulation VBO will now be visible in the dropdown.
Set the path where the image file needs to be saved.
View attachment 1539158025959.png

- Setting the Output parameter as below will give a data item containing the full path and file name of the Image in case you need it anywhere in the diagram
View attachment 1539158083005.png

- After running the Process, an Image file will be stored in the path that was given in the above parameter.
It is saved by today's date and time. (Ignore the file name shown in the screenshot since my system has incorrect date settings)

- This is how the image looks with great clarity when opened in an image editor
If you would like to save the image as png then in the above code stage replace .jpeg with .png.
View attachment 1539158378019.png

Let me know if you are facing any Reference issues or anything.
 

sivagelli

Well-Known Member
@CD58 you can use Calc stage with expression GetClipboard() and Store in to a Data item.
OR use Utility Environment VBO and action Get Clipboard to get the content on clipboard to a Data item.
 
Top