locked
Display as Icon RRS feed

  • Question

  • Greetings - 

    I am having an issue with inserting/attaching files/objects as icons. I am unsure as to what may have happened. I have some code that inserts a file as an object and should display as an icon. The code worked beautifully until recently. Again not sure what happened as my code has not changed.

    Code works great for images. However, now when selecting any type of file other than an image file, the file does not display as an icon as requested in the code :( All image files display correctly as an icon.

    Anyone have any ideas as to what may have happened and/or how I might correct this? I have checked all of my settings as well and do not see any changes.

    Here is my code:

    Option Explicit

    Private Sub Attach1_Click()

    '''''''''''''''''''''''''''''''''''''''''''''''

    ' Inserts 1st file into sheet                 '

    '''''''''''''''''''''''''''''''''''''''''''''''

        Dim FileName As Variant

       

        ActiveWindow.DisplayWorkbookTabs = False

       

        Sheets("Attachments").Unprotect Password:=""

       

        Sheets("Attachments").Range("A2").Select

        'get file name from open file dialog box.

        FileName = Application.GetOpenFilename(FileFilter:="", Title:="Choose file to attach")

       

        'if no file chosen then close sub

        If FileName = False Then

            MsgBox ("No file chosen")

            Exit Sub

        End If

       

        'insert file into sheet.

        Sheets("Attachments").OLEObjects.Add FileName:=FileName, Link:=False, _

                DisplayAsIcon:=True

          

            Sheets("Attachments").Attach1.Visible = False

            Sheets("Attachments").RemoveAttach1.Visible = True

       

        'ActiveWorkbook.Save

        Sheets("Attachments").Protect Password:="", DrawingObjects:=False, Contents:=True, Scenarios:= _

        True

           

    End Sub

    Here are some screen shots....

    Monday, June 27, 2016 6:37 PM

Answers

  • I love this place! Thank you for the excellent suggestion Edward. Gave me an idea that works beautifully :)

    Used your method with a little twist on the IconLabel...works perfect. 

    Here is what I ended up with...

    Sheets("Attachments").OLEObjects.Add FileName:=FileName, Link:=False, _
                DisplayAsIcon:=True, IconFileName:= _
            "C:\windows\system32\packager.dll", IconIndex:=0, IconLabel:=FileName

    Thank you again Edward for your assistance :) Much appreciated!

    • Marked as answer by rstreets2 Tuesday, June 28, 2016 4:37 PM
    Tuesday, June 28, 2016 4:36 PM

All replies

  • Hi rstreets,

    >> However, now when selecting any type of file other than an image file, the file does not display as an icon as requested in the code

    Based on your code, your issue is caused by that you did not set IconFileName. As OLEObjects.Add Method (Excel), if IconFileName isn’t specified or the file contains no icons, the default icon for the OLE class is used. It seems the default icon for image is correct, and the default icon for word is not what we want.

    I suggest you set the IconFileName when you insert other file attachments.

    Here is a simple code for word.

    ActiveSheet.OLEObjects.Add(FileName:="C:\Users\xxx\Desktop\Test.docx", _
            Link:=False, DisplayAsIcon:=True, IconFileName:= _
            "C:\Windows\Installer\{90160000-000F-0000-0000-0000000FF1CE}\wordicon.exe", _
            IconIndex:=0, IconLabel:="C:\Users\xxx\Desktop\Test.docx").Select

    To get IconFileName like above, you could insert the file manually, and then record the steps to get the value for IconFileName, or you could set IconFileName with your own icon.

    Best Regards,

    Edward


    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.


    Tuesday, June 28, 2016 5:46 AM
  • I love this place! Thank you for the excellent suggestion Edward. Gave me an idea that works beautifully :)

    Used your method with a little twist on the IconLabel...works perfect. 

    Here is what I ended up with...

    Sheets("Attachments").OLEObjects.Add FileName:=FileName, Link:=False, _
                DisplayAsIcon:=True, IconFileName:= _
            "C:\windows\system32\packager.dll", IconIndex:=0, IconLabel:=FileName

    Thank you again Edward for your assistance :) Much appreciated!

    • Marked as answer by rstreets2 Tuesday, June 28, 2016 4:37 PM
    Tuesday, June 28, 2016 4:36 PM