0

I have a document with VBA that inserts images based on file paths, but when I save down the file and email to other parties the images are not visible. The document is used as a master doc. The file paths for each image are determined by a forumla in each cell, so they vary from cell to cell and doc to doc - so using the file path within the code is not possible.

I would like to embed the images so that once the file is saved the images are available to anyone who opens the file. Right now I am manually selecting all objects and doing a copy paste to work around the issue. If there is a way to code the select all objects and copy paste that might work, but everything I have found to do this paste the images in a different location to where they were originally inserted...

Here is the code I am using to insert the images initially:

Sub URLInsertPicture()
    Dim Pshp As Shape
    Dim xRg As Range
    Dim xCol As Long

    On Error Resume Next
    Application.ScreenUpdating = False
    Set Rng = ActiveSheet.Range("B52")
    For Each cell In Rng
    filenam = cell
    ActiveSheet.Pictures.Insert(filenam).Select
    Set Pshp = Selection.ShapeRange.Item(1)
    If Pshp Is Nothing Then GoTo lab
    xCol = cell.Column
    Set xRg = Cells(cell.Row, xCol)
    With Selection
            .ShapeRange.LockAspectRatio = msoTrue
            If (.Height \ .Width) <= (Rng.Height \ Rng.Width) Then
                .Width = Rng.Width - 1
                .Left = Rng.Left + 1
                .Top = Rng.Top + ((Rng.Height - Selection.Height) / 2)
            Else
                .Top = Rng.Top + 1
                .Height = Rng.Height - 1
                .Left = Rng.Left + ((Rng.Width - Selection.Width) / 2)
            End If

            .Placement = xlMoveAndSize
            .PrintObject = True
    End With
lab:
    Set Pshp = Nothing
    Range("B52").Select
    Next
    Application.ScreenUpdating = True
End Sub

It would be most appreciated if you could provide your answers as a modification of my code as the other variables must remain unchanged and I am new to VBA.

Thank you

slkmfg
  • 1
  • 1
  • https://stackoverflow.com/questions/17110425/vba-to-insert-embedded-picture-excel – Tim Williams Oct 10 '19 at 04:14
  • Possible duplicate of [VBA to insert embedded picture excel](https://stackoverflow.com/questions/17110425/vba-to-insert-embedded-picture-excel) – Cindy Meister Oct 10 '19 at 08:48
  • Thannk you both. I have viewed this thread before. The problem is it requires me to specify the file location within the code, where my file location is picked up from each cell that requires an images inserted (so it varies for every image and every document). – slkmfg Oct 10 '19 at 21:54
  • You say the file paths are created using a formula - you can't use that same value to load the picture as embedded instead of linked? – Tim Williams Oct 13 '19 at 04:37
  • @TimWilliams I tired this but I think I must be doing something wrong? Would you be able to show me how to make this change? – slkmfg Oct 15 '19 at 22:32

0 Answers0