VBA code for excel

excelme Posts 1 Registration date Saturday June 18, 2016 Status Member Last seen June 18, 2016 - Jun 18, 2016 at 07:46 AM
 Blocked Profile - Jun 18, 2016 at 01:11 PM
Dear Expert,

Hope you will be in good health!

This is Santosh from India (Gurgaon).

I need your help to get images saved in the excel sheet only using VBA code.

I want to save images in excel, currently all the images are being linked by it.

In excel 2007, there is no issue.

When I send this file to another person, images get Disappeared.

VBA code is being mentioned below, request you to please make the rectification per my requirement.


Sub ProcessFiles()
Dim sPath As String, s As String, r As Range
Dim shp As ShapeRange
Dim c As Range, cell As Range, sname As String
Dim p As Picture, diffwidth As Double, diffHeight As Double

sPath = "C:\Users\santosh\Desktop\E Catalogue\Images\New folder"

If Right(sPath, 1) <> "\" Then sPath = sPath & "\"

Set r = Range("B2", Cells(Rows.Count, 2).End(xlUp))

For Each cell In r
cell.Offset(0, 1).Select

Set c = cell.Offset(0, -1)
s = sPath & cell.Value & ".jpg" 'remove the .jpg if the cell contains the extension
sname = Dir(s)

If sname <> "" Then
Set p = ActiveSheet.Pictures.Insert(s)
Set shp = p.ShapeRange

diffwidth = c.Width - p.Width

If diffwidth > 0 Then
p.Left = c.Left + 0.5 * diffwidth
p.Left = c.Left
End If

diffHeight = c.Height - p.Height
If diffHeight > 0 Then
p.Top = c.Top + 0.5 * diffHeight
p.Top = c.Top
End If

c.Value = "Not Available"

End If

End Sub

1 response

Blocked Profile
Jun 18, 2016 at 01:11 PM
Save the images on a file share, and link the sheet the common file share!

So, use mapped drives and point the links to the Mapped drive that everyone is accessing! That is simple fix!

It's kind of fun to do the impossible! -Walter Elias Disney