问题(据我所知)是not您的设备,但它位于托管图像的服务器上,并且无法返回文档。我不确定上面 Tim 的评论(与 206 响应代码有关)来自哪里,但如果是这种情况,或者 URL 返回一些错误代码,那么您的 VBA 也会失败,并且可能你无能为力解决这个问题如果问题出在主机上。
我今天手动输入网址下载文件,没问题。
我检查响应码它正确返回 200(成功)。
此时您能做的最好的事情就是简单地捕获错误,并将其标记为以后检查。
在我的测试中,我故意使用了一些错误的 URL,只是为了确保错误处理按预期工作。这些是唯一对我来说失败的。
这是我使用的代码,仅对您的代码进行了轻微修改,并包含一个错误处理程序,用于向 URL 返回错误的单元格添加注释。这样您就可以稍后手动查看并根据需要添加这些图像。
Sub InstallPictures()
Dim i As Long
Dim v As String
Dim cl As Range
Dim pic As Shape
Dim errors As New Collection
i = 2
Set cl = Cells(i, 15)
Do While Trim(cl.Value) <> vbNullString
v = Trim(cl.Value)
cl.ClearComments
With ActiveSheet.Pictures
On Error GoTo ErrHandler
Set p = .Insert(Trim(v))
On Error GoTo 0
' I added this code to resize & arrange the pictures
' you can remove it if you don't need it
p.TopLeftCell = cl.Offset(0, -1)
p.Top = cl.Offset(0, -1).Top
p.Left = cl.Offset(0, -1).Left
p.Height = Cells(i, 15).Height
p.Width = Cells(1, 15).Width
'''''''''''''''''''''''''''''
End With
NextCell:
i = i + 1
Set cl = Cells(i, 15)
Loop
If errors.Count > 0 Then
MsgBox "There were errors, please review the comments as some files may need to be manually downloaded"
End If
Exit Sub
ErrHandler:
Call ErrorNote(v, cl, errors)
Resume NextCell
End Sub
Private Sub ErrorNote(url$, cl As Range, ByRef errs As Collection)
' Adds an item to the errs collection and flags the offending
' cell with a Comment indicating the error occurred.
On Error Resume Next
errs.Add (url)
With cl
.ClearComments
.AddComment ("Error with URL: " & vbCrLf & url)
End With
End Sub