Hello,
I have a macro that searches a folder on my desktop for a particular image and then inserts it into a designated column.
Sku's listed are in column D. Respective images get inserted into column C.
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\Dan\Pictures\<wbr>inventory pictures\"
If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
Set r = Range("D1", Cells(Rows.Count, "D").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
'shp.ScaleHeight Factor:=0.5, RelativeToOriginalSize:=<wbr>msoTrue
shp.Height = 100
If shp.Height > 409 Then
cell.EntireRow.RowHeight = 409
Else
cell.EntireRow.RowHeight = shp.Height
End If
p.Left = c.Left
p.Top = c.Top
End If
Next
End Sub
This works fine in my workbook but when I send the file to a co-worker, they are unable to see the images. They get an error message where the images should be. Is this because the macro does not actually embed the image into the file?
Caveat: When the macro is run on an excel 2003 version (I am using 2013), it works fine and everyone I send the file to can see the images. I ready somewhere that there were some VBA compatibility issues for MS Office 2010 and higher.
Can anyone help solve this mystery?
Thanks!
I have a macro that searches a folder on my desktop for a particular image and then inserts it into a designated column.
Sku's listed are in column D. Respective images get inserted into column C.
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\Dan\Pictures\<wbr>inventory pictures\"
If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
Set r = Range("D1", Cells(Rows.Count, "D").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
'shp.ScaleHeight Factor:=0.5, RelativeToOriginalSize:=<wbr>msoTrue
shp.Height = 100
If shp.Height > 409 Then
cell.EntireRow.RowHeight = 409
Else
cell.EntireRow.RowHeight = shp.Height
End If
p.Left = c.Left
p.Top = c.Top
End If
Next
End Sub
This works fine in my workbook but when I send the file to a co-worker, they are unable to see the images. They get an error message where the images should be. Is this because the macro does not actually embed the image into the file?
Caveat: When the macro is run on an excel 2003 version (I am using 2013), it works fine and everyone I send the file to can see the images. I ready somewhere that there were some VBA compatibility issues for MS Office 2010 and higher.
Can anyone help solve this mystery?
Thanks!