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.