|
- Sub insertPic()
- '
- '删除已有图片
- Dim S1 As Shape '声明变量 as后 变量类型
- For Each S1 In ActiveSheet.Shapes 'ActiveSheet 代表活动工作簿中的,或者指定的窗口或工作簿中的活动工作表(最上面的工作表)。
- If S1.Type <> 8 Then 'type属性返回图表类型
- S1.Delete
- End If
- Next S1
-
- '插入图片
- '直接获取某列的值
- 'imgWidth = Range("A2")
- 'imgHeight = Range("B2")
- 'hColumn = Range("C2")
- 'imgPath = Range("D2")
-
- 'imgWidth = InputBox("请输入一个,大于等于1 数字", "设置图片插入列的列宽", 20)
- 'imgHeight = InputBox("请输入一个,大于等于1 数字", "设置图片插入行的行高", 40)
- 'hColumn = InputBox("请输入插入图片的所在列", "设置图片插入所在列的列数(大于等于 1 的整数)", 11)
-
- '通过自定义窗体获取数据
- imgWidth = imgSetting.ColumnWidth.Text
- imgHeight = imgSetting.RowHeight.Text
- hColumn = imgSetting.ColumnC.Text
- imgPath = imgSetting.imgPath.Text
- imgRow = imgSetting.imgRow.Text
-
- If Trim(hColumn) <> "" And Trim(imgWidth) <> "" And Trim(imgHeight) <> "" And Trim(imgPath) <> "" And Trim(imgRow) <> "" Then
- 'Fix 函数返回参数的整数部分
- If IsNumeric(hColumn) = True And IsNumeric(imgWidth) = True And IsNumeric(imgHeight) = True And hColumn >= 1 And imgWidth >= 1 And imgHeight >= 1 And IsNumeric(imgRow) = True And imgRow >= 1 And InStr(imgPath, "") > 0 Then
- If hColumn - Fix(hColumn) = 0 And imgRow - Fix(imgRow) = 0 Then
- imgWidth = CDbl(imgWidth)
- imgHeight = CDbl(imgHeight)
- hColumn = CInt(hColumn)
- Dim i As Integer
- Dim FilPath As String
- Dim rng As Range
- Dim S As String
- S = ""
- With Sheet1
- For i = 2 To .Range("a65536").End(xlUp).Row '查找A列从65536位置的单元格起,向上查找,直到找到最后一个非空单元格为止,并显示其行号
- If Trim(Cells(i, 1).Text) <> "" Then
- 'FilPath = ThisWorkbook.Path & "\photos" & .Cells(i, 1).Text & ".jpg"
- FilPath = imgPath & .Cells(i, 1).Text & ".jpg"
- If Dir(FilPath) <> "" Then
-
- .Pictures.Insert(FilPath).Select '选中
- Set rng = .Cells(i, hColumn)
- With Selection '当前的选择对象
- ActiveSheet.Rows(i).RowHeight = imgHeight '调整行高适合图片大小 Selection.ShapeRange.Height * imgHeight
- 'MsgBox ActiveSheet.Rows(i).RowHeight
- ActiveSheet.Columns(hColumn).ColumnWidth = imgWidth '粗略调整列宽适合图片大小 Selection.ShapeRange.Width * imgWidth
- .Top = rng.Top + 1
- .Left = rng.Left + 2
- .Width = rng.Width
- .Height = rng.Height
- End With
- Else
- S = S & Chr(10) & .Cells(i, 1).Text
- End If
- End If
- Next
-
- .Cells(hColumn, i).Select
- End With
- If S <> "" Then
- MsgBox S & Chr(10) & "没有照片"
- End If
- Else
- MsgBox "输入有误"
- End If
- Else
- MsgBox "输入有误"
- End If
- Else
- MsgBox "输入有误"
- End If
- End Sub
复制代码 上面代码中的删除图片把所有图片都给删除了,现在想根据 hColumn 这个变量的所指列删除本列的图片,怎么删除呀?
可以判断一下 每个 S1.left 是否大于插入列的Left坐标 并且 S1的右坐标及(S1.left+S1.width)的值是否 小于 插入列的left坐标+列宽, 如果满足条件,则删除即可.
|
|