使用VBA将在线图片插入Excel [英] Inserting an Online Picture to Excel with VBA

问题描述

我正在开展一个项目，需要通过URL填充图片单元格。所有网址都在一列中，我想在相邻的列中加载图像。我不是VBA专家，但是我发现一些代码有效，但是由于某些原因，我收到一个错误（通常是5个图像），说：

I'm currently working on a project and need to fill in cells with pictures via URLs. All URLs are in one column, and I'd like to load the images in an adjacent column. I'm no VBA expert, but I found some code that worked, but for some reason I get an error (usually 5 images in) that says:

运行时错误'1004'：
无法获取图片类的插入属性

Run-time error '1004': Unable to get the Insert property of the Pictures Class

再次，我正在使用一个系统，其中的URL是一列，即：

Again, I'm using a system where URLs are in one column i.e.:

xxxx.com/xxxx1.jpg

xxxx.com/xxxx1.jpg

xxxx.com/xxxx2.jpg

xxxx.com/xxxx2.jpg

xxxx.com/xxxx3.jpg

xxxx.com/xxxx3.jpg

xxxx.com/xxxx4.jpg

xxxx.com/xxxx4.jpg

通过一些搜索，我发现它可以链接到我的Excel版本（使用2010），虽然我不完全确定。

Through some searching, I found that it could be linked to my Excel version (using 2010), though I'm not completely sure.

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

Here's the current code I'm using:

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

推荐答案

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

This is an almost identical solution that I posted about a month ago:

Excel VBA插入图像从列的图像名称

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

这篇关于使用VBA将在线图片插入Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！