I need to dynamically add images to the excel sheet, however the images can't be tied to a specific path since the file is meant to be shared. By dynamically, i mean i want an image to be placed on the sheet when a function is called. I've made that part work, however if i move the picture files the images are lost in the sheet.

If i insert an image into excel, i can see that image stored in that excel files' media folder, however i can't figure out how to reference it.

I also thought about encoding the image to base64, then decode it from excel when the workbook is launched and place it somewhere in the filesystem, however i can't figure out how to do that either since all of the examples only show decoding a string, not an entire file.

To clarify: i want the pictures to persist regardless if i have them on my filesystem or not, just like they do when i insert them manually.

The code for inserting images:

Sub InsertPictureHyperlink(row As Integer, column As Integer, path As String, extnsn As String)
    Dim image As Picture
    Dim image_path As String
    Dim image_name As String
    Dim appDataPath As String
    image_path = Environ$("AppData")   "\appres\"   extnsn   ".png"
    With ActiveSheet.Cells(row, column)
        Set image = ActiveSheet.Pictures.Insert(CStr(image_path))
        image.Top = .Top
        image.Left = .Left
        image.ShapeRange.LockAspectRatio = msoFalse
        image.Placement = xlMoveAndSize
        image.ShapeRange.Width = 25
        image.ShapeRange.Height = 25
    End With
    With ActiveSheet
        .Hyperlinks.Add Anchor:=.Shapes(image.name), Address:=path
    End With
    ActiveCell.Value = ""
End Sub

CodePudding user response：

If you need to transport the pictures with the Excel file and you don't want them as pictures in an extra folder, a solution can be to insert all the pictures into a hidden worksheet and copy them from there onto the desired sheet later.

This way your pictures are saved within the Excel file at any time.