Excel从URL插入图片的宏无法正常运行，仅提前打开对应URL才可生效

• 图片站点校验规则升级：当前多数站点对非浏览器发起的图片请求增加了Referer、Cookie、User-Agent校验，未携带合法标识的请求会直接被拦截。手动访问URL后本地会留存站点临时Cookie，Office内置请求组件可复用该Cookie完成请求，因此手动访问后可正常插入，未访问的请求会被拒绝。
• 原代码能力缺陷：你使用的Pictures.Insert方法依赖Office内置的极简HTTP请求能力，不支持自定义请求头适配站点校验规则；且代码中On Error Resume Next会吞掉所有请求错误，无法定位具体失败原因，同时代码存在变量未声明、循环逻辑不完整、无空URL占位图适配逻辑的问题。
• 系统安全策略更新：近期Windows/Office的安全更新可能拦截了未授信的网络请求，仅手动访问过的URL会被标记为安全，允许Office加载对应资源。

该方案使用WinHttpRequest模拟浏览器请求，携带标准请求头绕过站点校验，同时补全了空URL时的占位图逻辑、错误捕获逻辑，采用晚绑定模式无需额外配置引用：

Sub URLPictureInsert()
    Dim shp As Shape
    Dim Pshp As Shape
    Dim xRg As Range
    Dim xCol As Long
    Dim Pic As Object
    Dim Rng As Range
    Dim cell As Range
    Dim filenam As String
    Dim tempPath As String
    Dim WinHttp As Object
    Dim stream As Object
    Dim placeholderPath As String
    
    ' 配置占位图路径，修改为你本地星形占位图的完整路径即可
    placeholderPath = "C:\你的占位图存储路径\star_placeholder.png"
    
    Application.ScreenUpdating = False
    On Error GoTo ErrHandler
    
    ' 清空目标工作表原有图片
    Sheets("Message Template").Activate
    For Each Pic In ActiveSheet.Pictures
        Pic.Delete
    Next Pic
    For Each shp In ActiveSheet.Shapes
        shp.Delete
    Next shp
    
    ' 初始化请求和文件流对象，模拟Chrome浏览器请求特征
    Set WinHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
    Set stream = CreateObject("ADODB.Stream")
    tempPath = Environ("TEMP") & "\temp_pic_" & Format(Now(), "YYYYMMDDHHMMSS") & "_"
    
    xCol = 2
    Set Rng = Sheets("First Class").Range("E5:E500")
    
    For Each cell In Rng
        filenam = Trim(cell.Value)
        If filenam <> "" Then
            ' 发起HTTP请求携带标准头信息
            WinHttp.Open "GET", filenam, False
            WinHttp.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36"
            WinHttp.setRequestHeader "Referer", Left(filenam, InStr(8, filenam, "/") - 1)
            WinHttp.send
            
            ' 请求成功写入本地临时文件
            If WinHttp.Status = 200 Then
                stream.Open
                stream.Type = 1
                stream.Write WinHttp.responseBody
                stream.SaveToFile tempPath & cell.Row & ".png", 2
                stream.Close
                filenam = tempPath & cell.Row & ".png"
            Else
                ' 请求失败自动使用占位图
                filenam = placeholderPath
            End If
        Else
            ' 无URL时使用占位图
            filenam = placeholderPath
        End If
        
        ' 插入图片到指定单元格位置
        Set Pshp = ActiveSheet.Shapes.AddPicture(Filename:=filenam, LinkToFile:=msoFalse, SaveWithDocument:=msoTrue, Left:=0, Top:=0, Width:=-1, Height:=-1)
        
        If Not Pshp Is Nothing Then
            Sheets("Message Template").Activate
            Set xRg = Cells(2, xCol)
            Pshp.Placement = xlMoveAndSize
            With Pshp
                .Width = 100
                If .Height > xRg.Height Then .Height = xRg.Height
                .Top = xRg.Top + (xRg.Height - .Height) / 2
                .Left = xRg.Left + (xRg.Width - .Width) / 2
            End With
        End If
        xCol = xCol + 1
    Next cell
    
    ' 清理临时文件与对象
    Kill tempPath & "*.png"
    Set stream = Nothing
    Set WinHttp = Nothing
    Application.ScreenUpdating = True
    Exit Sub
    
ErrHandler:
    ' 异常场景自动 fallback 到占位图
    filenam = placeholderPath
    Resume Next
End Sub

1. 替换代码中placeholderPath的路径为你本地星形占位图的完整本地路径
2. 若图片为公司内部站点资源，可将站点域名加入Windows Internet选项的信任站点列表，降低安全拦截概率
3. 确认E列存储的是图片直链，而非图片预览页的HTML链接，直链结尾通常为.png/.jpg/.webp等图片格式后缀

内容的提问来源于stack exchange，提问作者Matthew Csorgo