Display as Icon

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

Answer 1

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.

Answer 2

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!