Code to replace embedded image with filename or url

[surfacescan]

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

[COLOR=#333333][FONT=arial]Public Sub Export()[/FONT][/COLOR]

[COLOR=#333333][FONT=arial]        Dim objTemp As Object[/FONT][/COLOR]
[COLOR=#333333][FONT=arial]        Dim objHolder As ChartObject[/FONT][/COLOR]
[COLOR=#333333][FONT=arial]        Dim sngWidth As Integer[/FONT][/COLOR]
[COLOR=#333333][FONT=arial]        Dim sngHeight As Integer[/FONT][/COLOR]
[COLOR=#333333][FONT=arial]        Dim TheFilename[/FONT][/COLOR]
[COLOR=#333333][FONT=arial]On Error GoTo skip[/FONT][/COLOR]


[COLOR=#333333][FONT=arial]TheFilename = Cells(3, 11).Value[/FONT][/COLOR]

[COLOR=#333333][FONT=arial]   'sets the picture as a temp object [/FONT][/COLOR]
[COLOR=#333333][FONT=arial]Set objTemp = ActiveSheet.Shapes(2)[/FONT][/COLOR]

[COLOR=#333333][FONT=arial]ActiveSheet.Shapes(2).Select[/FONT][/COLOR]

[COLOR=#333333][FONT=arial]    Selection.ShapeRange.ScaleHeight 1#, msoTrue, msoScaleFromTopLeft[/FONT][/COLOR]
[COLOR=#333333][FONT=arial]    Selection.ShapeRange.ScaleWidth 1#, msoTrue, msoScaleFromTopLeft[/FONT][/COLOR]
[COLOR=#333333][FONT=arial]    sngWidth = objTemp.Width[/FONT][/COLOR]
[COLOR=#333333][FONT=arial]    sngHeight = objTemp.Height[/FONT][/COLOR]


[COLOR=#333333][FONT=arial] Charts.Add[/FONT][/COLOR]
[COLOR=#333333][FONT=arial]            ActiveChart.Location Where:=xlLocationAsObject, Name:=SheetNo[/FONT][/COLOR]
[COLOR=#333333][FONT=arial]            Set objHolder = ThisWorkbook.Worksheets("Sheet1").ChartObjects(1)[/FONT][/COLOR]


[COLOR=#333333][FONT=arial]With objHolder[/FONT][/COLOR]


[COLOR=#333333][FONT=arial]                    .Width = sngWidth + 20[/FONT][/COLOR]
[COLOR=#333333][FONT=arial]                    .Height = sngHeight + 20[/FONT][/COLOR]
[COLOR=#333333][FONT=arial]                    objTemp.Copy[/FONT][/COLOR]
[COLOR=#333333][FONT=arial]                End With[/FONT][/COLOR]


[COLOR=#333333][FONT=arial] With objHolder[/FONT][/COLOR]

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

[COLOR=#333333][FONT=arial]            End With[/FONT][/COLOR]
[COLOR=#333333][FONT=arial]skip:[/FONT][/COLOR]



[COLOR=#333333][FONT=arial]End Sub[/FONT][/COLOR]

 

[Andrew Poulsom]

Welcome to MrExcel.

That code copies the picture to a chart and exports the chart. As far as I know that's the only way to save a picture to disk, unless you use Stephen Bullen's PastePicture code:

Office Automation Ltd. - Stephen Bullen's Excel Page

 

[surfacescan]

Welcome to MrExcel.

That code copies the picture to a chart and exports the chart. As far as I know that's the only way to save a picture to disk, unless you use Stephen Bullen's PastePicture code:

Office Automation Ltd. - Stephen Bullen's Excel Page

Thanks for the response. I have noticed that when I open my .xlsx file using 7zip or another archize utility, it puts all the photos into a folder called media. This is a great step - but I dont see any way to link those photos to rows in the spreadsheet (ie: unique filename).