个人信件
logo

Excel批量插入图片

在填写报表时,有时需要需要插入大量的图片,我们可以利用VBA编码来减少重复操作。以excel 2007为例,假如需要从第五行开始,在第2、3列或者4、5列或者6、7列或者8、9列或者10、11列一次插入两列图片,操作步骤如下:

依次点击“工具”—“宏”—“visual basic编辑器”,进入代码编辑窗口,双击左上角项目中的“Sheet1”,输入如下代码:


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 1 And Target.Row > 4 Then
'Cancel = True '如果事件过程将此参数设为 True,则程序结束后不执行默认的双击操作
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False '单选择
.Filters.Clear '清除文件过滤器
' .Filters.Add “图片”, “*.jpg;*.png”
.InitialFileName = ActiveWorkbook.Path
If .Show = -1 Then 'FileDialog 对象的 Show 方法显示对话框,并且返回 -1(如果您按 OK)和 0(如果您按 Cancel)。
i2 = 0
i3 = 0
Setfso = CreateObject(“Scripting.filesystemobject”) '取目标文件
Setmyf = fso.getfolder(.SelectedItems(1))
For Each i In myf.Files
If i3 Mod 2 = 0 Then
Cells(Target.Row + i2, Target.Column).Value = Target.Row + i2 ActiveSheet.Shapes.AddPicture(.SelectedItems(1) & "\" & i.Name, msoCTrue, msoCTrue, Cells(Target.Row + i2, Target.Column).Left, Cells(Target.Row + i2, Target.Column).Top, Cells(Target.Row + i2, Target.Column).Width, Cells(Target.Row + i2, Target.Column).Height).AlternativeText = "Error"
Else
ActiveSheet.Shapes.AddPicture(.SelectedItems(1) & "\" & i.Name, msoCTrue, msoCTrue, Cells(Target.Row + i2, Target.Column + 1).Left, Cells(Target.Row + i2, Target.Column + 1).Top, Cells(Target.Row + i2, Target.Column + 1).Width, Cells(Target.Row + i2, Target.Column + 1).Height).AlternativeText = "Error"
i2 = i2 + 1
End If
i3 = i3 + 1
Next
Range("A" & Target.Row + i2).Select
End If
End With
If Target.Column = 2 Or Target.Column = 4 Or Target.Column = 6 Or Target.Column = 8 Or Target.Column = 10 Then
Select Case Target.Column
Case 2:
dd = "B"
Case 4:
dd = "D"
Case 6:
dd = "F"
Case 8:
dd = "H"
Case 10:
dd = "J"
End Select
j3 = Application.CountA(Sheets(2).Range(dd & ":" & dd))
Cells(4, Target.Column).Value = j3 - 3
End If
End If
End Sub

本文系作者原创,转载请注明出处,原文链接:http://www.kjzwt.cn


阅读:287
  
123 3条记录 第2页/共3