EXCEL VBA 檔案夾操作——批量添加指定檔案夾的圖檔

這個小程式的前提是指定的檔案夾裡裝的全部是圖檔格式的檔案

步驟1：

添加一個filesystemobject引用，工具--->引用---->microsoft scripting runtime

步驟2：

添加子產品，并添加以下代碼

Option Explicit
'設定單元格大小，單元格大小設定因人而異，設定數量要大于或者等于檔案夾圖檔數量，這裡範圍為range（"a1:h10")的單元格的大小
Sub changecell()
Dim r As Integer, c As Integer
For r = 1 To 10
    For c = 1 To 8
        With Sheet1.Cells(r, c)
             .RowHeight = 100
             .ColumnWidth = 15
        End With
    Next c
Next r
End Sub
 
'添加圖像
Sub addpic()
Dim fso As Scripting.FileSystemObject
Dim file As file, files As files
Dim picname()
Dim i As Integer
Dim rng As Range
Set fso = New Scripting.FileSystemObject
Set files = fso.GetFolder("F:\My Pictures\").files
ReDim picname(files.Count)
For Each file In files
    i = i + 1
    picname(i) = file.Path
Next
i = 1
For Each rng In Sheet1.Range("a1:h10")
    With rng
         Sheet1.Shapes.AddPicture picname(i), msoTrue, msoTrue, .Left, .Top, .Width, .Height
    End With
    i = i + 1
Next
End Sub
 
'删除圖像
Sub deletepic()
Dim i As Integer
For i = 1 To Sheet1.Shapes.Count
    Sheet1.Shapes(1).Delete
Next
End Sub
           

'設定單元格大小，單元格大小設定因人而異，設定數量要大于或者等于檔案夾圖檔數量，這裡範圍為range（"a1:h10")的單元格的大小程式效果：