vba - 使用 VBA 将在线图片插入 Excel

Question

我目前正在做一个项目，需要通过 URL 用图片填充单元格。所有 URL 都在一列中，我想将图像加载到相邻列中。我不是 VBA 专家，但我发现了一些有效的代码，但由于某种原因，我收到一个错误(通常是 5 张图片)，上面写着:

运行时错误“1004”:
无法获取图片类的插入属性

同样，我正在使用一个 URL 位于一列中的系统，即:

xxxx.com/xxxx1.jpg

xxxx.com/xxxx2.jpg

xxxx.com/xxxx3.jpg

xxxx.com/xxxx4.jpg

通过一些搜索，我发现它可以链接到我的 Excel 版本(使用 2010)，尽管我不完全确定。

这是我正在使用的当前代码:

Sub URLPictureInsert()
Dim cell, shp As Shape, target As Range
    Set Rng = ActiveSheet.Range("a5:a50") ' range with URLs
    For Each cell In Rng
       filenam = cell
       ActiveSheet.Pictures.Insert(filenam).Select

  Set shp = Selection.ShapeRange.Item(1)
   With shp
      .LockAspectRatio = msoTrue
      .Width = 100
      .Height = 100
      .Cut
   End With
   Cells(cell.Row, cell.Column + 1).PasteSpecial
Next

End Sub

任何帮助将非常感激!

原始代码来源: http://www.mrexcel.com/forum/excel-questions/659968-insert-image-into-cell-url-macro.html

Answer 1

这是我大约一个月前发布的几乎相同的解决方案:

Excel VBA Insert Images From Image Name in Column

Sub InsertPic()
Dim pic As String 'file path of pic
Dim myPicture As Picture 'embedded pic
Dim rng As Range 'range over which we will iterate
Dim cl As Range 'iterator

Set rng = Range("B1:B7")  '<~~ Modify this range as needed. Assumes image link URL in column A.
For Each cl In rng
pic = cl.Offset(0, -1)

    Set myPicture = ActiveSheet.Pictures.Insert(pic)
    '
    'you can play with this to manipulate the size & position of the picture.
    ' currently this shrinks the picture to fit inside the cell.
    With myPicture
        .ShapeRange.LockAspectRatio = msoFalse
        .Width = cl.Width
        .Height = cl.Height
        .Top = Rows(cl.Row).Top
        .Left = Columns(cl.Column).Left
    End With
    '

 Next

 End Sub