Chnage embedded images to filename/url

surfacescan · October 30th, 2012, 04:45 AM

Hi - this is my first post on the board but I couldnt find the answer anywhere so thought I would try a post.

I have 4000+ survey responses and many of the rows have a photo of the respondent (optional). I need to extract these embedded images and put them online so I can access them as a url.

I have a simple row of data and at column H (optionally) has an embedded image. I want to be able to:
a) export that image to a folder
b) insert the filename for the above image to column I?

I have found some VBA code on this board (here) that provided a similar solution for exporting images as the background of a cell... However, I cannot follow the logic and I cant help but think there may be a simpler way of doing this... (code posted below).

Thanks for any advice or tips on creating a simpler function for this.

I am using Excel 2007 on a windows 7 home OS.

Code:

Public Sub Export()

        Dim objTemp As Object
        Dim objHolder As ChartObject
        Dim sngWidth As Integer
        Dim sngHeight As Integer
        Dim TheFilename
On Error GoTo skip


TheFilename = Cells(3, 11).Value

   'sets the picture as a temp object 
Set objTemp = ActiveSheet.Shapes(2)

ActiveSheet.Shapes(2).Select

    Selection.ShapeRange.ScaleHeight 1#, msoTrue, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleWidth 1#, msoTrue, msoScaleFromTopLeft
    sngWidth = objTemp.Width
    sngHeight = objTemp.Height


 Charts.Add
            ActiveChart.Location Where:=xlLocationAsObject, Name:=SheetNo
            Set objHolder = ThisWorkbook.Worksheets("Sheet1").ChartObjects(1)


With objHolder


                    .Width = sngWidth + 20
                    .Height = sngHeight + 20
                    objTemp.Copy
                End With


 With objHolder

                .Chart.Paste
                With .Chart.Shapes(1)
                    .Placement = xlMove
                    .Left = -4
                    .Top = -4
                End With
                .Width = sngWidth
                .Height = sngHeight
                .Chart.Export Filename:="C:\Photos\" & TheFilename & ".jpg", FilterName:="JPG"
                .Chart.Shapes(1).Delete

            End With
skip:



End Sub