Hi,
I've run into a problem that I suspect is easy to fix if you know more of the inner workings of this than I do. I'm pretty new with VBA.
I have a macro that runs when I press a button on the sheet. The macro deletes all images in the sheet - this works no problem. Then, it goes through a range of cells and imports an image from the URL that was in that cell. This is where the problem starts. It works, sometimes.
The error message is the "broken path"-image, but as I describe below, when there is a broken path it fails quietly.
I've tried to find a pattern.
If the URL points to a place where there is no image, it fails (as it should)
If the URL points to a place on the internet (i googles cat and pasted in a "copy image address") it sometimes works (the same image always or never works)
If the URL points to a local image it sometimes works (the same image always or never works)
- If I then drag and drop one of the images from the Finder into the sheet it shows up. I then delete that image. Then, when pressing the refresh button. The image appears as intended.
My guess is this has more to do with how Excel and/or VBA works with images and paths then with the code?
Any ideas of what I'm doing wrong
Here is the whole block
Sub URLPictureInsert()
'delete all images'
Dim Pic As Object
For Each Pic In ActiveSheet.Pictures
Pic.Delete
Next Pic
'get images and place them in cell to the right of url'
Dim Pshp As Shape
Dim xRg As Range
Dim xCol As Long
On Error Resume Next
Application.ScreenUpdating = False
'define range of URL, first and last cell'
Set Rng = ActiveSheet.Range("D3:D40")
For Each cell In Rng
filenam = cell
ActiveSheet.Pictures.Insert(filenam).Select
Set Pshp = Selection.ShapeRange.Item(1)
If Pshp Is Nothing Then GoTo lab
xCol = cell.Column + 1
Set xRg = Cells(cell.Row, xCol)
With Pshp
.LockAspectRatio = msoFalse
If .Width > xRg.Width Then .Width = xRg.Width * 2 / 3
If .Height > xRg.Height Then .Height = xRg.Height * 2 / 3
.Top = xRg.Top + (xRg.Height - .Height) / 2
.Left = xRg.Left + (xRg.Width - .Width) / 2
End With
lab: Set Pshp = Nothing
Range("A1").Select
Next Application.ScreenUpdating = True
End Sub
I've also uploaded a quick video of when I drag an image in to excel, deletes it and then after that can import it with the macro, https://youtu.be/H0YJkighJMg _
as I said, I'm new to VBA and I'm sure it's an easy fix, not really sure what I should search for.