1.首先打开Excel;
2.按快捷键“Alt”+"F11"启动宏,点击插入,选择模块,输入以下代码:
Sub InsertPictureObjectsAsIcons()
Dim ws As Worksheet
Dim PicPath As String
Dim PicName As String
Dim i As Integer, j As Integer
Dim PicIndex As Integer
Dim rng As Range
Dim oleObj As Object
' 修改为你的工作表名称
Set ws = ThisWorkbook.Sheets("Sheet1")
' 修改为你的图片文件夹路径
PicPath = "C:\Users\FQ\Desktop\1\"
' 初始化图片索引
PicIndex = 1
' 遍历指定范围内的单元格
For i = 10 To 11
For j = 3 To 17
' 构建图片名称
PicName = PicPath & PicIndex & ".jpg" ' 假设图片名为 1.jpg, 2.jpg, ...
' 检查图片是否存在
If Dir(PicName) <> "" Then
' 获取当前单元格
Set rng = ws.Cells(i, j)
' 插入对象并显示为图标
On Error Resume Next
Set oleObj = ws.OLEObjects.Add(Filename:=PicName, Link:=False, DisplayAsIcon:=True, _
IconFileName:="C:\Windows\System32\imageres.dll", _
IconIndex:=3, IconLabel:="Picture" & PicIndex)
On Error GoTo 0
' 检查对象是否成功插入
If Not oleObj Is Nothing Then
' 调整对象位置和大小
With oleObj
.Left = rng.Left
.Top = rng.Top
.Width = rng.Width / 1 ' 调整图标宽度
.Height = rng.Height / 1' 调整图标高度
End With
Else
MsgBox "未能插入对象: " & PicName, vbExclamation
End If
End If
' 增加图片索引
PicIndex = PicIndex + 1
' 当 PicIndex 超过 30 时退出循环
If PicIndex > 30 Then Exit Sub
Next j
Next i
End Sub
输入代码后的界面:
代码相关解释:
Excel表格的名字查看及图片的路径和后缀名查看:
输入后代码后关闭宏,然后按“Alt”+“F8”,运行“InsertPictureObjectsAsIcons”,点击“执行”
插入的图片的具体大小可以自行根据代码调整,图标也可以更改