替换excel工作表中图片怎么保持图片位置和大小不变？
- 2022-07-03
- 来源/作者： Wps Office教程网/ 菜鸟图库
- 129 次浏览
Q：我原先在工作表中放置了一个图片，现在我想使用新的图片来替换该图片，但是要保持图片的位置和大小不变，如何使用VBA代码实现？
A：在VBE中插入一个标准模块，输入如下代码：
SubChangePicLoc()
On Error GoTo ErrHandle
Dim blnFlag As Boolean
Dim shp As Shape
Dim dblTop As Double
Dim dblLeft As Double
Dim dblHeight As Double
Dim dblWidth As Double
Dim FileToOpen As Variant
‘检查所选取的是形状还是单元格
blnFlag = False
For Each shp In ActiveSheet.Shapes
If shp.Name = Selection.Name Then
blnFlag = True
Exit For
Else
blnFlag = False
End If
Next shp
ErrHandle:
On Error GoTo endHandle
If blnFlag = True Then
With Selection.ShapeRange
‘保存所选图片的属性
dblTop = .Top
dblLeft = .Left
dblHeight = .Height
dblWidth = .Width
End With
‘删除所选图片
Selection.Copy
Selection.Delete
‘插入新图片
FileToOpen =Application.GetOpenFilename
If FileToOpen <> False Then
ActiveSheet.Pictures.Insert(FileToOpen).Select
Else
MsgBox “没有选择图片文件!”
ActiveSheet.Paste
End If
‘将原图片属性应用于新图片
With Selection.ShapeRange
.Top = dblTop
.Left = dblLeft
.Height = dblHeight
.Width = dblWidth
End With
Else
MsgBox “请选择图片,不要选择单元格区域.”
End If
endHandle:
End Sub
首先选取工作表中要替换的图片，然后运行代码，效果如下所示。
