Excel VBA提取图片目录主文件名去重时出现多余空行求助

Hey there! Let's fix that annoying empty row issue and get those unique main filenames into your Excel column cleanly. Here's a tailored solution for your scenario:

Step 1: Break Down Your Filename Pattern #

Your files fall into two clear groups:

• Original main files: PC_12321.jpg, HRK_23255.jpg (no trailing _X before the .jpg extension)
• Derivative files: PC_12321_1.jpg, HRK_23255_2.jpg (append _X where X is a sequential number)

Our goal is to capture each unique base name (like PC_12321) exactly once, with no gaps in the Excel column.

Step 2: Revised VBA Code #

This code uses a dictionary to track unique entries, skips duplicate derivative files, and writes results cleanly without empty rows:

Sub GetUniqueMainFilenames()
    Dim strDirectory As String
    Dim fname As String
    Dim mainFilename As String
    Dim lastUnderscorePos As Integer
    Dim dict As Object
    Dim outputRow As Integer
    
    ' Update this to your actual image folder path
    strDirectory = "C:\Your\Image\Folder\Path\"
    ' Ensure the path ends with a backslash
    If Right(strDirectory, 1) <> "\" Then strDirectory = strDirectory & "\"
    
    ' Initialize dictionary to track unique main filenames
    Set dict = CreateObject("Scripting.Dictionary")
    outputRow = 1 ' Start writing from row 1 (adjust to your target row)
    
    ' Loop through all .jpg files in the directory
    fname = Dir(strDirectory & "*.jpg")
    Do While fname <> ""
        ' Remove the .jpg extension first
        mainFilename = Left(fname, Len(fname) - 4)
        
        ' Check if the filename has a trailing _X (e.g., _1, _2)
        lastUnderscorePos = InStrRev(mainFilename, "_")
        If lastUnderscorePos > 0 Then
            ' Verify the part after the last underscore is a number
            If IsNumeric(Right(mainFilename, Len(mainFilename) - lastUnderscorePos)) Then
                ' Strip the _X suffix to get the main filename
                mainFilename = Left(mainFilename, lastUnderscorePos - 1)
            End If
        End If
        
        ' Only write the filename if it's not already in our dictionary
        If Not dict.Exists(mainFilename) Then
            dict.Add mainFilename, True
            ' Write to Column A (change the column number if needed)
            Cells(outputRow, 1).Value = mainFilename
            outputRow = outputRow + 1
        End If
        
        ' Grab the next file in the directory
        fname = Dir()
    Loop
    
    ' Clean up objects
    Set dict = Nothing
    MsgBox "Unique main filenames extracted successfully!", vbInformation
End Sub

Step 3: Key Fixes & Explanations #

• Dictionary for Uniqueness: The Scripting.Dictionary acts as a "unique list" checker — it ensures we never add the same main filename more than once, even if we process multiple derivative files (like PC_12321_1.jpg and PC_12321_2.jpg).
• Smart Filename Parsing: We detect and strip the trailing _X suffix only if it's a numeric sequence, so original main files stay untouched.
• No Empty Rows: We only increment the output row when we actually write a new unique filename, so there are no gaps between entries.

Step 4: How to Use #

1. Open your Excel workbook.
2. Press Alt + F11 to launch the VBA Editor.
3. Insert a new module (right-click your workbook in the Project Explorer > Insert > Module).
4. Paste the code above, then update the strDirectory line to match your image folder path.
5. Run the macro (press F5 or click the Run button in the editor).

This will give you a clean, gap-free list of unique main filenames in your target Excel column!

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