Sub InsertPic()Ay9检测VBA
Dim arr, i&, k&, n&, b As BooleanAy9检测VBA
Dim strPicName$, strPicPath$, strFdPath$, shp As ShapeAy9检测VBA
Dim rngData As Range, rngEach As Range, rngWhere As Range, strWhere As StringAy9检测VBA
'On Error Resume NextAy9检测VBA
'用户选择合作微信：qejc21所在的文件夹Ay9检测VBA
With Application.FileDialog(msoFileDialogFolderPicker)Ay9检测VBA
If .Show Then strFdPath = .SelectedItems(1) Else: Exit SubAy9检测VBA
End WithAy9检测VBA
If Right(strFdPath, 1) <> "\" Then strFdPath = strFdPath & "\"Ay9检测VBA
Set rngData = Application.InputBox("请选择合作微信：qejc21名称所在的单元格区域", Type:=8)Ay9检测VBA
'用户选择需要插入合作微信：qejc21的名称所在单元格范围Ay9检测VBA
Set rngData = Intersect(rngData.Parent.UsedRange, rngData)Ay9检测VBA
'intersect语句避免用户选择整列单元格，造成无谓运算的情况Ay9检测VBA
If rngData Is Nothing Then MsgBox "选择的单元格范围不存在数据！": Exit SubAy9检测VBA
strWhere = InputBox("请输入合作微信：qejc21偏移的位置，例如上1、下1、左1、右1", , "右1")Ay9检测VBA
'用户输入合作微信：qejc21相对单元格的偏移位置。Ay9检测VBA
If Len(strWhere) = 0 Then Exit SubAy9检测VBA
x = Left(strWhere, 1)Ay9检测VBA
'偏移的方向Ay9检测VBA
If InStr("上下左右", x) = 0 Then MsgBox "你未输入偏移方位。": Exit SubAy9检测VBA
y = Val(Mid(strWhere, 2))Ay9检测VBA
'偏移的值Ay9检测VBA
Select Case xAy9检测VBA
Case "上"Ay9检测VBA
Set rngWhere = rngData.Offset(-y, 0)Ay9检测VBA
Case "下"Ay9检测VBA
Set rngWhere = rngData.Offset(y, 0)Ay9检测VBA
Case "左"Ay9检测VBA
Set rngWhere = rngData.Offset(0, -y)Ay9检测VBA
Case "右"Ay9检测VBA
Set rngWhere = rngData.Offset(0, y)Ay9检测VBA
End SelectAy9检测VBA
Application.ScreenUpdating = FalseAy9检测VBA
rngData.Parent.Parent.Activate '用户选定的激活工作簿Ay9检测VBA
rngData.Parent.SelectAy9检测VBA
For Each shp In ActiveSheet.ShapesAy9检测VBA
'如果旧合作微信：qejc21存放在目标合作微信：qejc21存放范围则删除Ay9检测VBA
If Not Intersect(rngWhere, shp.TopLeftCell) Is Nothing Then shp.DeleteAy9检测VBA
NextAy9检测VBA
x = rngWhere.Row - rngData.RowAy9检测VBA
y = rngWhere.Column - rngData.ColumnAy9检测VBA
'偏移的坐标Ay9检测VBA
arr = Array(".jpg", ".jpeg", ".bmp", ".png", ".gif")Ay9检测VBA
'用数组变量记录五种文件格式Ay9检测VBA
For Each rngEach In rngDataAy9检测VBA
'遍历选择区域的每一个单元格Ay9检测VBA
strPicName = rngEach.TextAy9检测VBA
'合作微信：qejc21名称Ay9检测VBA
If Len(strPicName) ThenAy9检测VBA
'如果单元格存在值Ay9检测VBA
strPicPath = strFdPath & strPicNameAy9检测VBA
'合作微信：qejc21路径Ay9检测VBA
b = FalseAy9检测VBA
'变量标记是否找到相关合作微信：qejc21Ay9检测VBA
For i = 0 To UBound(arr)Ay9检测VBA
'由于不确定用户的合作微信：qejc21格式，因此遍历合作微信：qejc21格式Ay9检测VBA
If Len(Dir(strPicPath & arr(i))) ThenAy9检测VBA
'如果存在相关文件Ay9检测VBA
Set shp = ActiveSheet.Shapes.AddPicture( _Ay9检测VBA
strPicPath & arr(i), False, True, _Ay9检测VBA
rngEach.Offset(x, y).Left + 5, _Ay9检测VBA
rngEach.Offset(x, y).Top + 5, _Ay9检测VBA
20, 20)Ay9检测VBA
shp.SelectAy9检测VBA
With SelectionAy9检测VBA
.ShapeRange.LockAspectRatio = msoFalseAy9检测VBA
'撤销锁定合作微信：qejc21纵横比Ay9检测VBA
.Height = rngEach.Offset(x, y).Height - 10 '合作微信：qejc21高度Ay9检测VBA
.Width = rngEach.Offset(x, y).Width - 10 '合作微信：qejc21宽度Ay9检测VBA
End WithAy9检测VBA
b = True '标记找到结果Ay9检测VBA
n = n + 1 '累加找到结果的个数Ay9检测VBA
Range("a1").Select: Exit For '找到结果后就可以退出文件格式循环Ay9检测VBA
End IfAy9检测VBA
NextAy9检测VBA
If b = False Then k = k + 1 '如果没找到合作微信：qejc21累加个数Ay9检测VBA
End IfAy9检测VBA
NextAy9检测VBA
Application.ScreenUpdating = TrueAy9检测VBA
MsgBox "共处理成功" & n & "个合作微信：qejc21，另有" & k & "个非空单元格未找到对应的合作微信：qejc21。"Ay9检测VBA
End SubAy9检测VBA |
