none
not display pictures from excel 2007 to 2010 RRS feed

  • Question

  • Hi!

    I need your advise.

    Regarding the new excel 2010 during i'm using the macro for display pictures. Then I open in another computer the result is 

    "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"

    But in the excel 2007 everything fine the pictures is there. But from excel 2010 to 2007 the pictures dissappear. I need you advice regarding this code. And I hope is work also excel 2010. Please help me this solution.

    Option Explicit
    Sub DeleteAllPictures()
    Dim S As Shape
        For Each S In ActiveSheet.Shapes
        Select Case S.Type
        Case msoLinkedPicture, msoPicture
        S.Delete
        End Select
        Next
    End Sub
    Sub UpdatePictures()
    Dim R As Range
    Dim S As Shape
    Dim Path As String, FName As String
        Path = "F:\royal_plaza_wincash\"
        
        If Right(Path, 1) <> "\" Then Path = Path & "\"
        For Each R In Range("b1", Range("b" & Rows.Count).End(xlUp))
        
        Set S = GetShapeByName(R)
        If S Is Nothing Then
        
            FName = Dir(Path & R & ".jpg")
            
            If FName <> "" Then
            Set S = InsertPicturePrim(Path & FName, R)
            End If
        End If
        If Not S Is Nothing Then
            If S.Name <> R Then R.Interior.Color = vbRed
            With R.Offset(0, -1)
            S.Top = .Top
            S.Left = .Left
            S.Width = .Width
            
            'S.LockAspectRatio = msoFalse
            
                If S.LockAspectRatio Then
                    If S.Height > .Height Then S.Height = .Height
                Else
                    S.Height = .Height
                End If
            End With
                S.ZOrder msoSendToBack
                Else
                R.Offset(0, -1) = "NO PICTURE AVAILABLE"
            End If
        Next
    End Sub
    Private Function GetShapeByName(ByVal SName As String) As Shape
        On Error Resume Next
        Set GetShapeByName = ActiveSheet.Shapes(SName)
    End Function
    Private Function InsertPicturePrim(ByVal FName As String, ByVal SName As String) As Shape
    Dim P As Picture
        On Error Resume Next
        Set P = ActiveSheet.Pictures.Insert(FName)
        If Not P Is Nothing Then
        Set InsertPicturePrim = P.ShapeRange(1)
        P.Name = SName
        End If
    End Function

    Wednesday, January 13, 2016 3:16 PM

Answers

All replies

  • Hi,

    As this thread is about the code, I will move it to the Excel for Developers forum.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Melon Chen
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Thursday, January 14, 2016 6:40 AM
  • Hi jctata,

    Based on my test in excel 2010, it works fine, but the pictures are linked to the original files, so if you open that excel in other computer, it can’t load the pictures if these pictures aren’t exist in corresponding path.

    Please use Shapes.AddPicture method to add picture to excel file.

    There is a thread that may benefit you:

    # VBA to insert embeded picture excel

    http://stackoverflow.com/questions/17110425/vba-to-insert-embeded-picture-excel

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 15, 2016 2:59 AM
    Moderator