Macro no longer working with new computer

[sclin16]

Hi,

I have the below macro and just got a new work comp. New comp is unable to run my macro now. I think it might be a Windows 10 issue? I'm also running Office 365. Can anyone please help? I essentially need to have a macro look up a number in column E, find the corresponding jpg in a specific folder, and populate that image in column B.

Thanks in advance for your help!

Sub Kok()
On Error Resume Next
Dim i%, ppath$, sku$


For i = 6 To 500
 sku = Cells(i, 5)
 ppath = "H:\Retail\E-Commerce\Photo Shoots" & sku & "" & sku & "_00_r17.jpg"


With ActiveSheet.Pictures.Insert(ppath)
With .ShapeRange
.LockAspectRatio = msoTrue
.Width = 30
.Height = 40
End With
.Left = ActiveSheet.Cells(i, 2).Left
.Top = ActiveSheet.Cells(i, 2).Top
.Placement = 1
.PrintObject = True
End With
Next
End Sub

 

[Joe4]

Good catch, Fluff. That line would suppress all errors!

 

[Fluff]

It's the first thing I tend to look for when something "Doesn't work"

 

[Joe4]

It's the first thing I tend to look for when something "Doesn't work"

Smart - I should have done the same!

 

[sclin16]

What happens if you remove the line in red & than run the code

Sub Kok()
[COLOR=#ff0000]On Error Resume Next[/COLOR]
Dim i%, ppath$, sku$


For i = 6 To 500
 sku = Cells(i, 5)
 ppath = "H:\Retail\E-Commerce\Photo Shoots" & sku & "" & sku & "_00_r17.jpg"


With ActiveSheet.Pictures.Insert(ppath)
With .ShapeRange
.LockAspectRatio = msoTrue
.Width = 30
.Height = 40
End With
.Left = ActiveSheet.Cells(i, 2).Left
.Top = ActiveSheet.Cells(i, 2).Top
.Placement = 1
.PrintObject = True
End With
Next
End Sub

I get a Run-time error '1004':
Unable to get the Insert property of the Pictures class.

 

[sclin16]

When I run it, Excel crashes and when I reopen Excel, it looks like 5 of the pictures have populated correctly. What's strange is that the macro works about 25% of the time on my new comp.

 

[Fluff]

That sounds like some of the pictures don't exist in the folder.

 

[sclin16]

Yeah, that's why I have the "On Error Resume Next" line.

 

[Fluff]

How about

Sub Kok()
Dim i%, ppath$, sku$


For i = 6 To 500
   sku = Cells(i, 5)
   ppath = "H:\Retail\E-Commerce\Photo Shoots" & sku & "" & sku & "_00_r17.jpg"

   If Dir(ppath) <> "" Then
      With ActiveSheet.Pictures.Insert(ppath)
         With .ShapeRange
            .LockAspectRatio = msoTrue
            .Width = 30
            .Height = 40
         End With
         .Left = ActiveSheet.Cells(i, 2).Left
         .Top = ActiveSheet.Cells(i, 2).Top
         .Placement = 1
         .PrintObject = True
      End With
   End If
Next
End Sub

 

[sclin16]

OMG, thank you so much! It works! Just for my knowledge, what's the difference between my macro and the one that you just provided? Still a newbie at this whole thing and would love to learn from my mistakes.

 

[Fluff]

This line

If Dir(ppath) <> "" Then

Checks to see if the file exists, if it does then the picture is added to the sheet, if the picture doesn't exist the code jumps to the End If & then loops back to check the next picture.
With your code the "On Error Resume Next" just masks all & any errors which can cause further problems.