Excel VBA从列中的图像名称插入图像 [英] Excel VBA Insert Images From Image Name in Column

问题描述

如果不同的线程关于插入图像并重新调整大小，但是找不到一个完全符合我想要做的事情，那么阅读很多。

Been reading a lot if different threads about inserting images and re-sizing them but cannot find one that does exactly what I want it to do.

所以说我具有2行的电子表格。列A是图像列，列B是图像名称。

So say I have a spreadsheet with 2 rows. Column A which is the image column and Column B which is the Image Name.

我想要一个脚本，它将遍历列B中的每个值，插入图像将该名称匹配到列A上的同一行，调整大小以适应150高18宽的单元格大小，然后转到下一行并重复通过电子表格。

I want to have a script that will run through each value in column B, insert the image that matches that name into the same Row on Column A, Resize it to fit the size of the cell which is 150 high by 18 wide, then move on to the next row and repeat through the spreadsheet.

推荐答案

下面是一个示例，它将遍历一系列可以修改的单元格（B1：B100），并使用单元格中的文件名列左（从列A），并将图像的尺寸设置为适合B列中的单元格。

Here is a sample that will iterate over a range of cells (B1:B100) which you can modify, and uses the filename from the cell one column to the left (so, from Column A), and sizes the image to fit within cell in column B.

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("B7:B7")
For Each cl In rng
    pic = cl.Offset(0, -1)

        Set myPicture = ActiveSheet.Pictures.Insert(pic)
        '
        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

此代码中没有错误处理来解释无效的文件名你可能想补充一下。

There is no error-handling in this code to account for invalid filenames, you will probably want to add that.

这篇关于Excel VBA从列中的图像名称插入图像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！