get image from excel

[davejam]

Hi all,

one of my colleagues has built hundreds of product specs including images in spreadsheets.

Unfortunately this is seperate to our system and of no use to what we are trying to do!!!

I have managed to programatically open the file and get the data out of the cells (luckilly the files are uniform in layout) but when it comes to getting the images i am struggling.

after research i can get reference the images using a loop getting each image ID

xlWorksheet.Shapes.item(1).id

where xlWorksheet is the document worksheet.

I am struggling to move onto the next step of actually grabbing the image and saving it to database or even putting it into an image variable!!

If you can point me in the right direction it would be greatly appreciated.

Cheers

daveJam

it works on my machine, so technically i win!

 

[SkipVought]

hi,

I do not know .net syntax, but I can tell you that there is in the Worksheet Object, a Shapes Collection and that each Shape object can be access like this in VB...

dim sp as Excel.Shape
for each sp in xlWorksheet.Shapes
   sp.copy
   'now do something with this
next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[davejam]

Thats a great help, thats the error checking on the number of images sorted for me.

My problem is i don't know what to do with the item once i have reference to it, simply trying to put it in an image variable doesn't want to work.

How do i work with the sp.copy once i have it as none of the intellisense objects seem to give me what i need, or do i need to do some kind of format or transfer to get it into a variable to either save it to database or file?

Cheers

Thank you

daveJam

it works on my machine, so technically i win!

 

[jebenson]

This should do it.

Dim appExcel As Excel.Application
        Dim xlsBook As Excel.Workbook
        Dim xlsSheet As Excel.Worksheet

        Dim img As Image

        appExcel = New Excel.Application

        xlsBook = appExcel.Workbooks.Open("C:\Documents and Settings\ebenson.TPFA\My Documents\Book1.xlsx")

        xlsSheet = xlsBook.Worksheets("Sheet1")


        For Each xlsShape As Excel.Shape In xlsSheet.Shapes
            xlsShape.Copy()

            MsgBox(xlsShape.Name)

            If Clipboard.ContainsImage Then
                img = Clipboard.GetImage

                PictureBox1.Image = img
            End If
        Next




        If Not xlsBook Is Nothing Then
            xlsBook.Close(False)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsBook)
            xlsBook = Nothing

            appExcel.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel)
            appExcel = Nothing
        End If

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[davejam]

thats great but not sure if i'm missing something, when it gets to

If Clipboard.ContainsImage Then

it picks up nothing!

i've got code as follows

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim xlPic As Image
        xlApp = New excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open("c:\mydoc.xls")
        xlWorkSheet = xlWorkBook.Worksheets("PRODUCT DETAILS")
        'display the cells value B2
        MsgBox(xlWorkSheet.Cells(2, 3).value)
        For Each sp As excel.Shape In xlWorkSheet.Shapes
            sp.Copy()
            MsgBox(sp.Name)
            If Clipboard.ContainsImage Then
                xlPic = Clipboard.GetImage
                '                PictureBox1.Image = img
            End If

        Next
        '        xlWorkSheet.Cells(2, 3) = "blah blah blah"
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

    End Sub

daveJam

it works on my machine, so technically i win!

 

[jebenson]

I think that

xlApp = New excel.ApplicationClass

should be

xlApp = New excel.Application


Also, what does MsgBox(sp.Name) display?

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[davejam]

cheers,

changed to xlApp = New excel.Application but nothing is changed

when it scrolls through MsgBox(sp.Name) it displays the name of each imnage

in this test case being Image11 through 16!

so this would suggest its finding everything properly but its when its playing with the clipboard there is an issue.

Another turn up is once i have ran the code, if i open paint and paste (ctrl+v) i get the last image that i would be expecting... so it is getting as far as the clipboard.

I am using vs2005 version 8.0.50727.867 on vista sp2 if that is any different!!

I will have a play, there must be something simple going on here

daveJam

it works on my machine, so technically i win!

 

[jebenson]

Instead of opening each one, try creating an array of Image objects and put each image into one of the array objects.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[davejam]

if i step the code and paste into paint after every sp.copy it pastes the expected image, yet it is not picking it up from the

clipboard.containsimage

if in the process i copy some text to clipboard (ctrl+C) and test with

If clipboard.containstext then

it picks this up.

if, whilst stepping in code, I goto paint and highlight image and copy (ctrl+C) then go back to code it picks up clipboard.containsimage....

So in summing up, sp.copy puts the image onto the clipboard (proven by pasting into paint) but it does not register when testing against code...

not sure where to go from here!!!

daveJam

it works on my machine, so technically i win!

 

[davejam]

if i step the code and paste into paint after every sp.copy it pastes the expected image, yet it is not picking it up from the

clipboard.containsimage

if in the process i copy some text to clipboard (ctrl+C) and test with

If clipboard.containstext then

it picks this up.

if, whilst stepping in code, I goto paint and highlight image and copy (ctrl+C) then go back to code it picks up clipboard.containsimage....

So in summing up, sp.copy puts the image onto the clipboard (proven by pasting into paint) but it does not register when testing against code...

not sure where to go from here!!!

daveJam

it works on my machine, so technically i win!

 

[davejam]

if i step the code and paste into paint after every sp.copy it pastes the expected image, so its not how it is picking up the images, yet it is not picking it up from the

clipboard.containsimage

if in the process i copy some text to clipboard (ctrl+C) and test with

If clipboard.containstext then

it picks this up.

if, whilst stepping in code, I goto paint and highlight image and copy (ctrl+C) then go back to code it picks up clipboard.containsimage....

So in summing up, sp.copy puts the image onto the clipboard (proven by pasting into paint) but it does not register when testing against code...not sure where to go from here!!!

daveJam

it works on my machine, so technically i win!