Write images to excel
- Thread starter sidram.m
- Start date
Hi sidram.m,
You might have to either write a VBA macro code and then call it from BP
OR have your own customised action using a code stage within the VBO that will insert an image into the Excel file.
Both the codes are just of one line...rest all are create workbook/worksheet objects.
Below is the code for the action in the MS Excel VBO
You need to initialise the sheet object which you can do so by looking at the other actions in the MS Excel VBO.
- PicturePath will be the parameter passed to the Object with the file name included which then has to be passed to the Code stage Input parameters
- cellLeft and cellTop: these are to be used only if you need the image inserted into a specific cell.
Pass the cell Reference (eg; A10) as a parameter to the object. Then in the code stage get the Left and Top values of that cell and use it in these two variables.
If you do not want to insert the image in a specific cell then leave them as 0
- Similarly to keep the image in its original width and height set them to -1.
- SaveWithDocument is -1 for True
- LinkToFile is 0 for False
You might have to either write a VBA macro code and then call it from BP
OR have your own customised action using a code stage within the VBO that will insert an image into the Excel file.
Both the codes are just of one line...rest all are create workbook/worksheet objects.
Below is the code for the action in the MS Excel VBO
Code:
sheet.Shapes.AddPicture (Filename:=PicturePath, LinkToFile:=0, _
SaveWithDocument:= -1, Left:=cellLeft, Top:=cellTop, Width:=-1, Height:=-1)You need to initialise the sheet object which you can do so by looking at the other actions in the MS Excel VBO.
- PicturePath will be the parameter passed to the Object with the file name included which then has to be passed to the Code stage Input parameters
- cellLeft and cellTop: these are to be used only if you need the image inserted into a specific cell.
Pass the cell Reference (eg; A10) as a parameter to the object. Then in the code stage get the Left and Top values of that cell and use it in these two variables.
If you do not want to insert the image in a specific cell then leave them as 0
- Similarly to keep the image in its original width and height set them to -1.
- SaveWithDocument is -1 for True
- LinkToFile is 0 for False
Manishgarg3005
New Member
Hi, Could you please tell in detail how to write code in blueprism, i donot know much about coding.If you help me with it
Hi Manishgarg3005,
Each of the below links in the forum contains an explanation of how to use the Code stage using VB/VBA.
It is not that difficult given that there already exists a VB code for all the actions in the MS Excel VBO and you just need to make few modifications to it. Also, as you might have experienced, the internet is a vast resource and lots of code to develop any kind of feature is already available on the web. You would just need to know (or learn to know) how to incorporate that code into Blue Prism which would come if you start doing it.
I have given some explanation in these posts on how to start by making a copy of the existing action pages.
I assume you are looking to code for adding the image to Excel. So in your case you just need to create the necessary objects like sheet and workbook (which you will get from the below links) and you are all set to go.
I suggest you go through the links, start coding for your process, if you face any difficulties myself or anyone with the relevant information will be able to help you with it.
http://rpaforum.net/threads/how-can-i-read-or-write-data-from-footer-of-excel-using-blueprism.1075/
http://rpaforum.net/threads/excel-cell-color-identification.1039
http://rpaforum.net/threads/excel-vbo.1031/
http://rpaforum.net/threads/filter-excel-data-by-specific-criteria.992/post-2912
http://rpaforum.net/threads/vba-excel-sorting.509/post-2808
http://rpaforum.net/threads/besoin-d-aide.869/post-2584
http://rpaforum.net/threads/besoin-d-aide.869/post-2821
Each of the below links in the forum contains an explanation of how to use the Code stage using VB/VBA.
It is not that difficult given that there already exists a VB code for all the actions in the MS Excel VBO and you just need to make few modifications to it. Also, as you might have experienced, the internet is a vast resource and lots of code to develop any kind of feature is already available on the web. You would just need to know (or learn to know) how to incorporate that code into Blue Prism which would come if you start doing it.
I have given some explanation in these posts on how to start by making a copy of the existing action pages.
I assume you are looking to code for adding the image to Excel. So in your case you just need to create the necessary objects like sheet and workbook (which you will get from the below links) and you are all set to go.
I suggest you go through the links, start coding for your process, if you face any difficulties myself or anyone with the relevant information will be able to help you with it.
http://rpaforum.net/threads/how-can-i-read-or-write-data-from-footer-of-excel-using-blueprism.1075/
http://rpaforum.net/threads/excel-cell-color-identification.1039
http://rpaforum.net/threads/excel-vbo.1031/
http://rpaforum.net/threads/filter-excel-data-by-specific-criteria.992/post-2912
http://rpaforum.net/threads/vba-excel-sorting.509/post-2808
http://rpaforum.net/threads/besoin-d-aide.869/post-2584
http://rpaforum.net/threads/besoin-d-aide.869/post-2821
radhavydehig
New Member
Hello ..i am trying to write image to Excel... (I am very new to code
)
as i m trying to execute with this Syntax it is showing me some errors...
sheet.Shapes.AddPicture (Filename:=PicturePath, LinkToFile:=0, _
SaveWithDocument:= -1, Left:=cellLeft, Top:=cellTop, Width:=-1, Height:=-1)
and I am unable to execute it ... could anyone please show it with an example... (Showing code)
and also do we need to define any libraries at initialize stage to execute it
Kindly help me
)as i m trying to execute with this Syntax it is showing me some errors...
sheet.Shapes.AddPicture (Filename:=PicturePath, LinkToFile:=0, _
SaveWithDocument:= -1, Left:=cellLeft, Top:=cellTop, Width:=-1, Height:=-1)
and I am unable to execute it ... could anyone please show it with an example... (Showing code)
and also do we need to define any libraries at initialize stage to execute it
Kindly help me
Hi radhavydehig,
You will need to create a new action in the Ms Excel VBO.
Here is the Code stage for it.
Refer to the file names for the explanation of the below attachments.
You will need to create a new action in the Ms Excel VBO.
Here is the Code stage for it.
Code:
Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet As Object
dim cellLeft, cellTop as integer
Try
sw = GetWorkbook(Handle, Source_Workbook)
ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)
sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet
sheet.Range(Cell_to_Paste_in).select()
Dim cell as Object = excel.ActiveCell
cellLeft = cell.Left
cellTop = cell.Top
sheet.Shapes.AddPicture (Filename:=PicturePath, LinkToFile:=0, _
SaveWithDocument:= -1, Left:=cellLeft, Top:=cellTop, Width:=-1, Height:=-1)
Success = True
Catch e As Exception
Success = False
Message = e.Message
Finally
sw = Nothing
ss = Nothing
dw = Nothing
ds = Nothing
excel = Nothing
sheet = Nothing
End TryRefer to the file names for the explanation of the below attachments.
Attachments
Sukesh Kumaru
Active Member
Hi,
For this purpose you have to find the first name length and use this syntax in expression editor
Mid([NAME], 1, 5)
PFA
Hi afzal29,
The Source Workbook data item should not be "Book 1" but in the format FileName.xlsx.
Either it needs to be an existing file or a new file that is saved so that Blue Prism will know where to paste the image.
Sukesh Kumaru
Active Member
Did you mentioned workbook path...
Hi BP_2018,
It looks like you are not writing the new action in the MS Excel VBO.
The very first line of the instructions is - "You will need to create a new action in the Ms Excel VBO"
The GetWorkbook and GetWorksheet functions are written in the Initialise tab of the Ms Excel VBO.
Hi BP_2018,
I think you read my message differently.
"It looks like you are not writing the new action in the MS Excel VBO"
You should write your new action in the MS Excel VBO by creating a new tab in it. This is because the GetWorkbook and GetWorksheet functions are already written in the Initialise tab of the Excel VBO.
Simply open the Ms Excel VBO. Right click on any of the tabs (choose the one that has the diagram and parameters similar to what you are designing and the one that will most suit your requirements so you don't have to make much changes). Once you right click there will be a Duplicate option. Then give a new name to this new Action eg; "Image to Excel". Also give a new name to the Code stage.
I think you read my message differently.
"It looks like you are not writing the new action in the MS Excel VBO"
You should write your new action in the MS Excel VBO by creating a new tab in it. This is because the GetWorkbook and GetWorksheet functions are already written in the Initialise tab of the Excel VBO.
Simply open the Ms Excel VBO. Right click on any of the tabs (choose the one that has the diagram and parameters similar to what you are designing and the one that will most suit your requirements so you don't have to make much changes). Once you right click there will be a Duplicate option. Then give a new name to this new Action eg; "Image to Excel". Also give a new name to the Code stage.
Hi BP_2018,
The error on the status bar in the screenshot shows as "Bad Handle" and the Handle data item is also empty.
Either the handle passed to this object is an incorrect one OR no handle is passed at all to the object parameters.
Ensure to pass the correct input/output parameters between the Process and the Object and all will work well.
In the exception stage i have set the exception type as bad handle PFA the screenshot for the same.
I have attached the error from the object code where i call the copy image action created in MS EXCEL VBO as well. In that i get "The given key was not present in the dictionary "
I have attached the error from the object code where i call the copy image action created in MS EXCEL VBO as well. In that i get "The given key was not present in the dictionary "