Insert Images based on Cell with File Path

sahrens1

New Member
Joined
Jun 24, 2017
Messages
37
All I want to do is is to insert an image that is 2"x 3"(wide) based on the path in another cell. If there is no file path, or the file path is incorrect, I just want it to skip that row.

The file path is in K, and the image location is in L... I have no hair on the right side of my head from all the errors I have had with what I thought would be a simple task.

Any help? Would be much appreciated.

Also, if possible, I would like to embed the images on save or close, etc.

Many thanks.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
The following might get you started...

Code:
Sub InsertPics_1019663()
'Pictures saved with file
'Set column width (ie, pic width) before running macro

Dim r As Range, Shrink As Long
Dim shpPic As Shape
Application.ScreenUpdating = False
Shrink = 0 'Provides negative offset from cell borders when > 0

On Error Resume Next
For Each r In Range("K1:K" & Cells(Rows.Count, 1).End(xlUp).Row)
    If r.Value <> "" Then
        Set shpPic = ActiveSheet.Shapes.AddPicture(Filename:=r.Value, linktofile:=msoFalse, _
            savewithdocument:=msoTrue, Left:=Cells(r.Row, 12).Left + Shrink, Top:=Cells(r.Row, 12).Top + Shrink, _
                Width:=-1, Height:=-1)
        With shpPic
            .LockAspectRatio = msoTrue
            .Width = Columns(12).Width - (2 * Shrink)
            Rows(r.Row).RowHeight = .Height + (2 * Shrink)
        End With
    End If
Next r
Application.ScreenUpdating = True
End Sub

The picture width is determined by the width of Column L, so drag or set the column width before running the macro.

Cheers,

tonyyy
 
Upvote 0
Thanks. So, it totally works for HTTP address, however it does not work for local address, like this. No error anything.. Thoughts?
Many, many thanks for your help.

The following might get you started...

Code:
Sub InsertPics_1019663()
'Pictures saved with file
'Set column width (ie, pic width) before running macro

Dim r As Range, Shrink As Long
Dim shpPic As Shape
Application.ScreenUpdating = False
Shrink = 0 'Provides negative offset from cell borders when > 0

On Error Resume Next
For Each r In Range("K1:K" & Cells(Rows.Count, 1).End(xlUp).Row)
    If r.Value <> "" Then
        Set shpPic = ActiveSheet.Shapes.AddPicture(Filename:=r.Value, linktofile:=msoFalse, _
            savewithdocument:=msoTrue, Left:=Cells(r.Row, 12).Left + Shrink, Top:=Cells(r.Row, 12).Top + Shrink, _
                Width:=-1, Height:=-1)
        With shpPic
            .LockAspectRatio = msoTrue
            .Width = Columns(12).Width - (2 * Shrink)
            Rows(r.Row).RowHeight = .Height + (2 * Shrink)
        End With
    End If
Next r
Application.ScreenUpdating = True
End Sub

The picture width is determined by the width of Column L, so drag or set the column width before running the macro.

Cheers,

tonyyy
 
Upvote 0
Could it be that the forward slashes should be back slashes? ie,
"C:\Users\SAHRENS\AppData\LocalLow\Google\GoogleEar\image_1.jpg"



<colgroup><col width="514"></colgroup><tbody>
</tbody>
And if you want to enable error messages, comment out or delete the line "On Error Resume Next"
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top