Problem importing photos into Excel 2010 RRS feed

  • Question

  • I wrote a workbook in Excel 2003 that imports photographs to worksheets within the workbook.  It works well in Excel 2003 and Excel 2007 but it does not work in Excel 2010.  I understand there is some sort of bug in 2010 that prevents this from happening.  I am not a seasoned VBA code writer so am at somewhat of a loss as to how my code should be changed.  Any help would be appreciated.

    The specific code instructions are as follows:

    Sub changePictures()

        'This macro will look for photos in the directory called c:\Claim Photos
        'If the directory doesn't exist, then the subroutine will create it.
        'James Falkener - 4/13/08
        On Error Resume Next
        MkDir "C:\Claim Photos"
        On Error GoTo 0

        Dim myPictureFrame As Shape
        Dim myFileName As Variant
        Dim curPath As String
        Dim myPictFolder As String
        Dim resp As Long
        'This chooses the folder where the photos are stored.  Must be the same as the MkDir folder above.
        myPictFolder = "C:\Claim Photos\"
        Set myPictureFrame = ActiveSheet.Shapes(Application.Caller)
        curPath = CurDir
        ChDrive myPictFolder
        ChDir myPictFolder
        myFileName = Application.GetOpenFilename
        If myFileName = False Then
            resp = MsgBox(prompt:="Do you want to remove the picture?", _
            If resp = vbYes Then
                myPictureFrame.DrawingObject.ShapeRange.Fill.Visible = msoFalse
                'do nothing--just quit
            End If
            myPictureFrame.DrawingObject.ShapeRange.Fill.UserPicture myFileName
        End If
        ChDrive curPath
        ChDrive curPath
    End Sub

    Friday, May 4, 2012 7:16 PM

All replies

  • I created a rectangle shape on a worksheet in Excel 2010 SP1 and assigned your macro to it. I only changed the path C:\Claim Photos to a path that exists on my PC.

    Clicking the shape and selecting a picture worked OK - the picture was displayed in the rectangle.

    So it's not a bug in Excel 2010, apparently.

    Regards, Hans Vogelaar

    Friday, May 4, 2012 7:48 PM
  • I have tried your explanation and also find it works.  The difference when I created the new worksheet was that I was asked to save it as a 'macro enabled' worksheet - an option that was not available in Excel 2003 (it just saved the worksheet macro enabled automatically).

    As I pointed out, I am somewhat of a novice hacker when it comes to VBA so your explanation and guidance is truly appreciated.

    Thanks so much


    Saturday, May 5, 2012 7:33 PM
  • The Excel 97-2003 .xls file type can be used for workbooks with macros and workbooks without macros. The separate .xlsx (without macros) and .xlsm (macro-enabled) file types were introduced in Excel 2007.

    Regards, Hans Vogelaar

    Saturday, May 5, 2012 8:30 PM
  • Many thanks.  One of the features in my macro is the ability to remove a photograph from the shape (frame).  If one does that, you then lose the 'solid fill' property of the shape (frame) and must right click on the frame and re-introduce the 'solid fill' property if another photograph is to be added at a later time.  Is there some way to maintain the 'solid fill' property if you choose to delete a picture ?



    Saturday, May 5, 2012 9:18 PM
  • Try this change to the part where you remove the picture:

            If resp = vbYes Then
                With myPictureFrame.DrawingObject.ShapeRange.Fill
                    .ForeColor.RGB = vbWhite ' or any other color
                End With

    Regards, Hans Vogelaar

    Saturday, May 5, 2012 9:56 PM
  • Works perfectly.  I am truly grateful for your help.

    All the best


    Sunday, May 6, 2012 12:48 AM