修改Excel VBA代码：替换插入图片为文件夹路径超链接

Got it, let's revamp your existing VBA code to add hyperlinks pointing to your image files instead of inserting the actual images—this will drastically reduce your Excel file size while still keeping easy access to the images intact.

Modified Complete Code #

Sub InsertImageHyperlinks()
    Dim ficimg As Variant
    Dim derlig As Long
    Dim i As Integer
    Dim Maplage As Range
    
    ' Get the last used row in column A (matches your original logic)
    derlig = Range("A" & Rows.Count).End(xlUp).Row
    
    ' Let user select multiple image files (adjust file types if needed)
    ficimg = Application.GetOpenFilename("Image Files (*.jpg;*.png;*.gif), *.jpg;*.png;*.gif", MultiSelect:=True)
    
    ' Exit if no files were picked
    If TypeName(ficimg) = "Boolean" Then Exit Sub
    
    ' Loop through each selected image file
    For i = LBound(ficimg) To UBound(ficimg)
        ' Set target cell (adjust column/row to your needs; example uses column B, next empty row)
        Set Maplage = Range("B" & derlig + i)
        
        ' Add hyperlink to the image path
        ActiveSheet.Hyperlinks.Add _
            Anchor:=Maplage, _
            Address:=ficimg(i), _
            TextToDisplay:=Dir(ficimg(i)) ' Shows only the filename (cleaner than full path)
        
        ' Optional: Format cell to look like a standard hyperlink
        Maplage.Font.Color = vbBlue
        Maplage.Font.Underline = xlUnderlineStyleSingle
    Next i
End Sub

Key Changes Explained #

• Removed image embedding logic: We stripped out the Ma_forme As Shape variable and all code that inserted images as shapes—no more embedding heavy image data into Excel.
• Core hyperlink method: The Hyperlinks.Add function does all the work here:
  • Anchor specifies which cell gets the hyperlink
  • Address uses the full file path of the image
  • TextToDisplay shows just the image filename (swap with ficimg(i) if you want the full path visible)
• Preserved your original workflow: The logic to find the last used row and loop through selected files stays the same, so you won't have to re-learn how the script works.
• Optional formatting: Added subtle styling to make hyperlinks easy to spot, which you can tweak or remove entirely.

Quick Tips #

• Adjust the Maplage range if you want hyperlinks in a different column (e.g., Range("C" & derlig + i) for column C).
• If you need to process all images in a folder instead of selecting them one by one, let me know and I can tweak the code to add a folder picker loop.

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