Check if Excel Sheet contains Image

[nikhiln]

Hello Everyone,

Is there a way I can check for Images in the Excel Worksheet.

------------------------------
Nikhil Negi
------------------------------

[RobertNew]

Hi Nikhil, 

you will need to create an action with a code stage and create one yourself, the code below is for saving images from an excel, so have a play with altering it to check.

Dim wb, ws As Object
Dim excel, sheet, range As Object
Dim FILEPATH as string
Dim TempImg as System.Drawing.Image
Dim sp as Object
FILEPATH = ""
dim i as integer
i = 0

Try
wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)
wb.Activate()
ws.Activate()
ShapeCount = ws.Shapes.Count
For Each sp In ws.Shapes
i = i + 1
FILEPATH = OutputPath & "Image" & i & ".png"
currentsh = i
if ws.Shapes.Item(i).Type = 13 then
'check if it exists already
If File.Exists(FILEPATH) = False then
ws.Shapes.Item(i).Copy
If Clipboard.ContainsImage Then
TempImg = Clipboard.GetImage()
TempImg.Save(FILEPATH,System.Drawing.Imaging.ImageFormat.png)
Threading.Thread.Sleep(100)
Clipboard.Clear()
Threading.Thread.Sleep(100)
else
End if
Else
End if
else
end if
'Clipboard.Clear()

Next
Clipboard.Clear()
Success = true

Catch e As Exception
Message = e.TargetSite.Tostring & ";" & e.Message
wb = Nothing
ws = Nothing
excel = Nothing
sheet = Nothing
range = Nothing
TempImg = nothing
Success = False
End Try

------------------------------
Robert New
Senior Mentor / Developer
Smart Automation Services Limited
Europe/London
------------------------------

[nikhiln]

Hi Robert, 
Can you also share the input and output parameters for the code stage

------------------------------
Nikhil Negi
------------------------------

[RobertNew]

here you go

------------------------------
Robert New
Senior Mentor / Developer
Smart Automation Services Limited
Europe/London
------------------------------

[nikhiln]

Unable to find the image.
Here is the screen shot of VBO

This is the message output after running the code
Message: TValue get_Item(TKey);The given key was not present in the dictionary.

------------------------------
Nikhil Negi
------------------------------

[RobertNew]

you will need to make sure the following are in the initialise stage. I can't actually remember where I got the imports for it, so after this you will just have to play with it, sorry.

------------------------------
Robert New
Senior Mentor / Developer
Smart Automation Services Limited
Europe/London
------------------------------

[nikhiln]

All the References and Namespace are there in the VBO by default, still I can't see any results

------------------------------
Nikhil Negi
------------------------------

[michaeloneil]

Hi Nikhil

If you just need to know if an image exists I previously created a vbo to list all images and output the names in a collection then i just loop the collection to see if the image name I want is listed. The code is below:

Dim wb, ws, excel, sheet As Object

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()

excel = ws.Application
sheet = excel.ActiveSheet

Dim Table As New DataTable
Table.Columns.Add("Name", GetType(String))

Dim image as Object

For Each image In wb.Worksheets(Worksheet).Shapes
Table.Rows.Add(image.Name)
Next

Images = Table
-----------------------

Inputs:
Handle
Workbook
Worksheet

Outputs:
Images

Hope this helps 🙂

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------

[nikhiln]

Hi Michael,
Thanks for the solution.
This is the error message i get when running the VBO

Internal : Could not execute code stage because exception thrown by code stage: The given key was not present in the dictionary.

------------------------------
Nikhil Negi
------------------------------

[michaeloneil]

Hi

The error you are getting is because the handle you are passing in isnt recognised in the vbo. The new action you have created to get the images did you create this as a single object or did you add this as an action in the existing Ms Excel object? If you have created it as a single object then the handle cant be passed in you need to create this in the same object you are using to create the instance and open the file.

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------