1

I am using below vba code to get images in excel sheet but this code add images in sheet as link, so when i am sending sheet to another pc that person get image location not found error.

How can i add attach images in sheet instead of link of image???

Sub AddOlEObject()
    Dim mainWorkBook As Workbook

    Set mainWorkBook = ActiveWorkbook
    Sheets("Object").Activate
    Folderpath = "C:\phoenix"
    Set fso = CreateObject("Scripting.FileSystemObject")
    NoOfFiles = fso.GetFolder(Folderpath).Files.Count
    Set listfiles = fso.GetFolder(Folderpath).Files

    For Each fls In listfiles
        strCompFilePath = Folderpath & "\" & Trim(fls.name)
        If strCompFilePath <> "" Then
            If (InStr(1, strCompFilePath, ".jpg", vbTextCompare) > 1) Then
                counter = counter + 1
                Sheets("Object").Range("A" & counter).Value = fls.name
                Sheets("Object").Range("B" & counter).ColumnWidth = 50
                Sheets("Object").Range("B" & counter).RowHeight = 150
                Sheets("Object").Range("B" & counter).Activate
                Call insert(strCompFilePath, counter)
                Sheets("Object").Activate
            End If
        End If
    Next

End Sub

Function insert(PicPath, counter)
    'MsgBox PicPath
    With ActiveSheet.Pictures.insert(PicPath)
        With .ShapeRange
            .LockAspectRatio = msoTrue
            .Width = 100
            .Height = 150
        End With
        .Left = ActiveSheet.Range("B" & counter).Left
        .Top = ActiveSheet.Range("B" & counter).Top
        .Placement = 1
        .PrintObject = True
    End With
End Function
Ambie
  • 4,872
  • 2
  • 12
  • 26
jatin verma
  • 177
  • 1
  • 3
  • 16
  • Possible duplicate of [VBA to insert embeded picture excel](http://stackoverflow.com/questions/17110425/vba-to-insert-embeded-picture-excel) – Robin Mackenzie Mar 06 '17 at 07:43

1 Answers1

0

Is the Image a single image that you have saved in a personal directory that you use frequently? Also is the image saved as .JPEG?

why don't you use a simple VBA code below?

Sub CALLPICTURE()


Worksheets("SHEET1").Shapes.AddPicture Filename:="I:\Control\DECOMP\ Images\Zebra.jpg", linktofile:=msoFalse, _
            savewithdocument:=msoCTrue, Left:=0, Top:=0, Width:=632, Height:=136


End Sub

You could add as many images as you want to.

Jaybreezy
  • 1
  • 7