VBA excel将指定文件夹中图片及名字插入

Sub yaohm()
Dim MyFile As String, MyPath As String, i As Integer, arr() As String
MyPath = "e:\temp\" '图片存放位置，可改
i = 11 '从第几行开始插入图片
MyFile = Dir(MyPath & "*.JPG") 'JPG是图片文件扩展名，必要时可以修改改

Do While Len(MyFile) > 0
i = i + 1
ReDim Preserve arr(i)
arr(i) = MyFile
MyFile = Dir

ActiveSheet.Pictures.Insert(MyPath & arr(i)).Select '插入图片
Selection.ShapeRange.Top = Cells(i, 1).Top '确定图片上部位置
Selection.ShapeRange.Left = Cells(i, 1).Left '确定图片左部位置
'Selection.ShapeRange.Width = Cells(i, 1).Width '确定图片宽度为单元格宽度
'Selection.ShapeRange.Height = Cells(i, 1).Height '确定图片高度为单元格高度
Selection.ShapeRange.Height = 60.75 '指定图片高度为任意值，建议这个值一般比单元格略小些

ActiveSheet.Cells(i, 2).Value = Left(arr(i), Len(arr(i)) - 4)

Loop

End Sub