1.首先,打开Excel
2.按“Alt”+"F11"打开宏,按照操作顺序进行插入模块,输入代码,然后关闭宏
3.确定自己电脑存放需要插入Excel文件的路径和命名好文件
Sub InsertExcelObjectsAsIcons()
Dim ws As Worksheet
Dim FilePath As String
Dim FileName As String
Dim i As Integer, j As Integer
Dim SampleIndex As Integer
Dim rng As Range
Dim oleObj As Object
' 修改为你的工作表名称
Set ws = ThisWorkbook.Sheets("Sheet1")
' 修改为你的Excel文件夹路径
FilePath = "C:\Users\FQ\Desktop\1\" ' 请将此路径修改为实际文件路径
' 初始化样机索引
SampleIndex = 1
' 遍历指定范围内的单元格
For Each rng In ws.Range("C24:Q24")
' 构建文件名称
FileName = FilePath & "iphone" & SampleIndex & ".xlsx"
' 检查文件是否存在
If Dir(FileName) <> "" Then
' 插入对象并显示为图标
On Error Resume Next
Set oleObj = ws.OLEObjects.Add(FileName:=FileName, Link:=False, DisplayAsIcon:=True, _
IconFileName:="C:\Windows\System32\shell32.dll", _
IconIndex:=3, IconLabel:="iphone" & SampleIndex)
On Error GoTo 0
' 检查对象是否成功插入
If Not oleObj Is Nothing Then
' 调整对象位置和大小
With oleObj
.Left = rng.Left
.Top = rng.Top
.Width = rng.Width / 1.1 ' 调整图标宽度
.Height = rng.Height / 1.1 ' 调整图标高度
End With
Else
MsgBox "未能插入对象: " & FileName, vbExclamation
End If
End If
' 增加样机索引
SampleIndex = SampleIndex + 1
' 当 SampleIndex 超过 15 时退出循环
If SampleIndex > 15 Then Exit Sub
Next rng
End Sub
代码解释:
在关闭宏后,按“Alt”+“F8”,文件已经插入成功,如下: