CODEWITHSUNDEEP
excelvba
oscar kjaer
oscar kjaer

Reputation: 3

Excel VBA -Insert image, but when I delete the image ind the explorer it dissapears in the excel sheet

The code works (sort of), but when I've inserted the image, I save the excel file and delete the foto in the file explorer. Then when I open then excel file again the image is gone.

Sub InsertImage()
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .ButtonName = "Submit"
        .Title = "Select an image file"
        .Filters.Clear
        .Filters.Add "JPG", "*.JPG"
        .Filters.Add "JPEG File Interchange Format", "*.JPEG"
        .Filters.Add "Graphics Interchange Format", "*.GIF"
        .Filters.Add "Portable Network Graphics", "*.PNG"
        .Filters.Add "Tag Image File Format", "*.TIFF"
        .Filters.Add "All Pictures", "*.*"

        If .Show = -1 Then
            Dim img As Object
            Set img = ActiveSheet.Pictures.Insert(.SelectedItems(1))

            'Scale image size
            'img.ShapeRange.ScaleWidth 0.75, msoFalse, msoScaleFromTopLeft
            'img.ShapeRange.ScaleHeight 0.75, msoFalse, msoScaleFromTopLeft

            'Position image
            img.Left = 300
            img.Top = 200

            'Set image sizes in points
            img.Width = 150
            img.Height = 150
        Else
            MsgBox ("Programet blev Annulleret")
        End If
    End With
End Sub

Isn't there a way to make the picture save with the excel file? So when I delete the original picture it is still in the excel file.

The error I get "The linked image cannot be displayed. The file may have been moved, renamed, or deleted. Verify that the link points to the correct file and location.

Upvotes: 0

Views: 1829

Answers (1)

Maldred
Maldred

Reputation: 1104

The picture is being inserted as a linked image, if you want the image to be saved in the excel spreadsheet you'll want it to be an embedded image

Sub InsertImage()

    Dim FullPathName as string

    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .ButtonName = "Submit"
        .Title = "Select an image file"
        .Filters.Clear
        .Filters.Add "JPG", "*.JPG"
        .Filters.Add "JPEG File Interchange Format", "*.JPEG"
        .Filters.Add "Graphics Interchange Format", "*.GIF"
        .Filters.Add "Portable Network Graphics", "*.PNG"
        .Filters.Add "Tag Image File Format", "*.TIFF"
        .Filters.Add "All Pictures", "*.*"

        If .Show = -1 Then
            '''''' Store the pathname of selected image to a variable
            FullPathName = .SelectedItems(1)

            '''''' Imports image as embedded into Worksheet
            Activesheet.Shapes.AddPicture filename:=FullPathName, _
            linktofile:=msoFalse, savewithdocument:=msoCTrue, _
            left:=300, _
            top:=200, _
            width:=150, _
            height:=150

            Dim Pic As shape
            For Each Pic In ActiveSheet.Shapes
               ''''' Change "A1" to which ever cell this is being placed in
               'If (Pic.left = Range("A1").left And Pic.top = Range("A1").top) Then
                  Pic.Select
                  Pic.LockAspectRatio = msoTrue
               'End If
            Next Pic

        Else
            MsgBox ("Programet blev Annulleret")
        End If
    End With
End Sub

Upvotes: 2

Related Questions