[已解决]批量插入图片

26827557

老师你好！我想根据“序号.学生姓名“来作为图片名称，批量插入图片到工作表单元格里，交较好能设置其属性。下面是段代码，能否帮我整理下

Dim a As Object
    Set a = ActiveSheet.Pictures.Insert(ThisWorkbook.Path & "\学生图片\" & Range("E10") & "\" & Range("E10") & ".jpg")
    a.Left = [L22].Left
    a.Top = [L22].Top
    a.Height = [L22:X33].Height

最佳答案

月排行榜 / 总排行榜

dsmch

2016-5-21 17:59

1. Sub Macro1()
   
2. mypath = ThisWorkbook.Path & ""
   
3. For Each m In ActiveSheet.Shapes
   
4.     If m.Type <> 8 Then m.Delete
   
5. Next
   
6. For i = 3 To Range("b65536").End(xlUp).Row
   
7.     Set c = Cells(i, 6)
   
8.     zf = Cells(i, 1) & "." & Cells(i, 2)
   
9.     x = c.Left: y = c.Top
   
10.     w = c.Width: h = c.Height
    
11.     ActiveSheet.Shapes.AddShape(msoShapeRectangle, x, y, w, h).Select
    
12.     Selection.ShapeRange.Fill.UserPicture mypath & zf & ".JPG"
    
13. Next
    
14. [a1].Activate
    
15. End Sub

复制代码

跳转到最佳答案楼层

dsmch

1. Sub Macro1()
   
2. mypath = ThisWorkbook.Path & ""
   
3. For Each m In ActiveSheet.Shapes
   
4.     If m.Type <> 8 Then m.Delete
   
5. Next
   
6. For i = 3 To Range("b65536").End(xlUp).Row
   
7.     Set c = Cells(i, 6)
   
8.     zf = Cells(i, 1) & "." & Cells(i, 2)
   
9.     x = c.Left: y = c.Top
   
10.     w = c.Width: h = c.Height
    
11.     ActiveSheet.Shapes.AddShape(msoShapeRectangle, x, y, w, h).Select
    
12.     Selection.ShapeRange.Fill.UserPicture mypath & zf & ".JPG"
    
13. Next
    
14. [a1].Activate
    
15. End Sub

复制代码

26827557

dsmch 发表于 2016-5-21 17:59

老师代码中以下有些不明白，能帮我注释下吗？

• Sub Macro1()
• mypath = ThisWorkbook.Path & "\"
• For Each m In ActiveSheet.Shapes
•     If m.Type <> 8 Then m.Delete  ‘是先删除所有图片？类型Type <> 8 这是什么意思？
• Next
• For i = 3 To Range("b65536").End(xlUp).Row
•     Set c = Cells(i, 6)
•     zf = Cells(i, 1) & "." & Cells(i, 2)
•     x = c.Left: y = c.Top
•     w = c.Width: h = c.Height
•     ActiveSheet.Shapes.AddShape(msoShapeRectangle, x, y, w, h).Select ‘先选择上？？？？？
•     Selection.ShapeRange.Fill.UserPicture mypath & zf & ".JPG" ’？？？？？？？？
• Next
• [a1].Activate ‘为什么要激活单元格呢
• End Sub
  

26827557

dsmch 发表于 2016-5-21 17:59

谢谢老师！理解了。