topic RE: Check if Excel Sheet contains Image in Product Forum

Check if Excel Sheet contains Image

nikhiln — Thu, 04 Feb 2021 12:27:00 GMT

Hello Everyone,

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

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

RE: Check if Excel Sheet contains Image

RobertNew — Thu, 04 Feb 2021 15:41:00 GMT

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
------------------------------

RE: Check if Excel Sheet contains Image

nikhiln — Thu, 04 Feb 2021 17:27:00 GMT

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

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

RE: Check if Excel Sheet contains Image

RobertNew — Thu, 04 Feb 2021 18:06:00 GMT

here you go

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

RE: Check if Excel Sheet contains Image

nikhiln — Thu, 04 Feb 2021 21:21:00 GMT

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
------------------------------

RE: Check if Excel Sheet contains Image

RobertNew — Thu, 04 Feb 2021 21:34:00 GMT

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
------------------------------

RE: Check if Excel Sheet contains Image

nikhiln — Thu, 04 Feb 2021 22:00:00 GMT

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

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

RE: Check if Excel Sheet contains Image

michaeloneil — Fri, 05 Feb 2021 09:25:00 GMT

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
------------------------------

RE: Check if Excel Sheet contains Image

nikhiln — Fri, 05 Feb 2021 09:38:00 GMT

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
------------------------------

RE: Check if Excel Sheet contains Image

michaeloneil — Fri, 05 Feb 2021 11:12:00 GMT

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
------------------------------

RE: Check if Excel Sheet contains Image

nikhiln — Fri, 05 Feb 2021 12:34:00 GMT

Thanks Michael for your suggestion

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

RE: Check if Excel Sheet contains Image

nikhiln — Fri, 12 Feb 2021 16:57:00 GMT

Hi Michael,
In my case The Output collection is huge and the process is taking a lot of time to find the image.
Is there a way I can look for an image in specific cell Range?

Thank you

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

RE: Check if Excel Sheet contains Image

michaeloneil — Tue, 16 Feb 2021 17:35:00 GMT

I would think it should be possible although you must have a very large number of images in your excel file if its taking that long to get them. It should only take a matter of seconds. You would need to amend the original code to get a range instead of just from a worksheet. You will need to add in range as an input and the part of the code getting the images would need to change to something like below. Note my_range is just the example input name.

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

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