VBA实现仅向Excel最后空行插入本地图片需求及代码求助

Got it, let's tweak your VBA code to fit exactly what you need—targeting only the last empty row in your sheet and skipping any images that have already been added. Here's the revised version with key improvements explained:

Sub Front_View()
    Dim targetCell As Range
    Dim existingShape As Shape
    Dim imagePath As String, fileName As String
    
    ' Set your image folder path
    imagePath = "C:\Users\Administrator\Downloads\1 Master Upload"
    ' Ensure path ends with a backslash to avoid file path errors
    If Right(imagePath, 1) <> "\" Then imagePath = imagePath & "\"
    
    ' Find the LAST EMPTY ROW in column C (right after the last row with data)
    Set targetCell = Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
    
    ' Skip if we're above your original starting row (C19)
    If targetCell.Row < 19 Then
        MsgBox "No empty rows available below row 19!", vbInformation
        Exit Sub
    End If
    
    ' Check if this cell's value already has a matching image shape
    Set existingShape = GetShapeByName(targetCell.Value)
    
    If existingShape Is Nothing Then
        ' Look for any image file matching the cell's value (supports all extensions)
        fileName = Dir(imagePath & targetCell.Value & ".*")
        
        If fileName <> "" Then
            ' Insert the picture and format it
            Set existingShape = InsertPicturePrim(imagePath & fileName, targetCell.Value)
            
            If Not existingShape Is Nothing Then
                ' Position/size the image to fit column D (next to the target cell)
                With targetCell.Offset(0, 1)
                    existingShape.Top = .Top
                    existingShape.Left = .Left
                    existingShape.Width = .Width
                    existingShape.LockAspectRatio = False
                    existingShape.Height = .Height
                End With
                existingShape.ZOrder msoSendToBack
                targetCell.Interior.Color = vbGreen ' Mark as successfully inserted
            Else
                targetCell.Offset(0, 1) = "Failed to insert picture"
            End If
        Else
            targetCell.Offset(0, 1) = "No picture available"
        End If
    Else
        ' Image already exists for this cell
        targetCell.Offset(0, 1) = "Picture already inserted"
        targetCell.Interior.Color = vbYellow
    End If
End Sub

Private Function GetShapeByName(ByVal shapeName As String) As Shape
    On Error Resume Next
    Set GetShapeByName = ActiveSheet.Shapes(shapeName)
    On Error GoTo 0 ' Reset error handling to catch other issues
End Function

Private Function InsertPicturePrim(ByVal filePath As String, ByVal shapeName As String) As Shape
    Dim pic As Picture
    On Error Resume Next
    Set pic = ActiveSheet.Pictures.Insert(filePath)
    On Error GoTo 0
    
    If Not pic Is Nothing Then
        Set InsertPicturePrim = pic.ShapeRange(1)
        InsertPicturePrim.Name = shapeName ' Name the shape to match the cell value for future checks
    End If
End Function

Key Changes & Explanations: #

• Target Only the Last Empty Row: Uses Range("C" & Rows.Count).End(xlUp).Offset(1, 0) to find the first empty row right after the last row with data in column C. We also added a check to avoid going above your original starting row (C19).
• Skip Existing Images: The GetShapeByName function checks if a shape with the same name as the target cell's value already exists. If it does, we skip insertion and mark the cell with a clear status.
• Better Error Handling: Added On Error GoTo 0 to reset error handling after shape lookups—this prevents masking unexpected errors later in the code.
• Clear Status Visuals: Uses green fill for successfully inserted images, yellow for existing ones, and plain text messages in column D to keep you informed.

How to Use: #

Just run the Front_View macro each time you need to add an image. It will only process the next empty row in column C, and never re-insert images that are already in the sheet.

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