如何使用VBA获取文件夹内文件名？含XLSM插入JPG图片场景需求

Hey there! Let's break down your two VBA tasks clearly — first pulling filenames from a specific folder, then inserting those randomly named JPGs into your XLSM file. Here's everything you need to make this work:

1. Retrieve Filenames from a Target Folder #

I'll share two reliable methods here: one that's quick and doesn't require extra library references, and another that's more flexible for advanced file operations.

Method 1: Use the Dir Function (No Extra References Needed) #

This is perfect for basic filename extraction. The code below will write all filenames from your target folder into column A of your active worksheet:

Sub GetFolderFileNames()
    Dim targetFolder As String
    Dim fileName As String
    Dim rowNum As Integer
    
    ' Option 1: Hardcode your folder path
    targetFolder = "C:\Your\Target\Folder\"
    ' Option 2: Let the user select the folder via a dialog box
    ' With Application.FileDialog(msoFileDialogFolderPicker)
    '     .Title = "Select the Folder to Extract Filenames From"
    '     If .Show = -1 Then targetFolder = .SelectedItems(1) & "\"
    ' End With
    
    rowNum = 1 ' Start writing from row 1
    fileName = Dir(targetFolder & "*.*") ' Grab the first filename
    
    ' Loop through all files in the folder
    Do While fileName <> ""
        Cells(rowNum, 1).Value = fileName
        rowNum = rowNum + 1
        fileName = Dir ' Get the next filename
    Loop
    
    MsgBox "Filenames successfully added to Column A!", vbInformation
End Sub

If you only want JPG files, replace *.* with *.jpg in the Dir call.

Method 2: Use FileSystemObject (More Flexible) #

This method lets you access additional file properties (like file size or creation date) if you need them. First, you'll need to enable the Microsoft Scripting Runtime library:

1. Open the VBA Editor
2. Go to Tools > References
3. Check the box for "Microsoft Scripting Runtime" and click OK

Then use this code:

Sub GetFileNamesWithFSO()
    Dim fso As New FileSystemObject
    Dim targetFolder As Folder
    Dim fileItem As File
    Dim rowNum As Integer
    
    ' Set your target folder (or use the dialog box option above)
    Set targetFolder = fso.GetFolder("C:\Your\Target\Folder\")
    
    rowNum = 1
    ' Loop through each file in the folder
    For Each fileItem In targetFolder.Files
        Cells(rowNum, 1).Value = fileItem.Name ' Filename
        Cells(rowNum, 2).Value = fileItem.Path ' Full file path
        rowNum = rowNum + 1
    Next fileItem
    
    ' Clean up objects
    Set fso = Nothing
    Set targetFolder = Nothing
    
    MsgBox "Filenames and paths added to Columns A & B!", vbInformation
End Sub

2. Insert Randomly Named JPGs into Your XLSM #

Assuming you've already extracted the JPG filenames into Column A (as shown above), this code will insert each corresponding image into Column B, adjusting the size to fit the cell:

Sub InsertJPGsIntoXLSM()
    Dim targetFolder As String
    Dim rowNum As Integer
    Dim imgPath As String
    Dim img As Picture
    
    ' Set the folder where your JPGs are stored
    targetFolder = "C:\Your\JPG\Folder\"
    ' Again, you can use the folder picker dialog here if preferred
    
    rowNum = 1
    ' Loop through each filename in Column A
    Do While Cells(rowNum, 1).Value <> ""
        imgPath = targetFolder & Cells(rowNum, 1).Value
        
        ' Check if the image file exists before trying to insert
        If Dir(imgPath) <> "" Then
            ' Insert the image into Column B
            Set img = ActiveSheet.Pictures.Insert(imgPath)
            
            ' Adjust image to fit the cell (keep aspect ratio)
            With img
                .Top = Cells(rowNum, 2).Top
                .Left = Cells(rowNum, 2).Left
                .ShapeRange.LockAspectRatio = msoTrue
                .ShapeRange.Height = Cells(rowNum, 2).Height
            End With
        Else
            Cells(rowNum, 2).Value = "Image not found"
        End If
        
        rowNum = rowNum + 1
    Loop
    
    Set img = Nothing
    MsgBox "Image insertion complete!", vbInformation
End Sub

Quick Notes: #

• Always double-check that your folder paths end with a backslash (\) — the dialog box option handles this automatically, but hardcoded paths need it.
• If you want to insert images into a specific sheet instead of the active one, replace ActiveSheet with ThisWorkbook.Sheets("YourSheetName").
• You can tweak the image sizing logic to fit your needs (e.g., match cell width instead of height, or set a fixed size).

内容的提问来源于stack exchange，提问作者Alvaro Morales