how to determine correct OLEObject Icon?

[bubba2413]

Hi everyone, have a little quandry here.

I've created a vba sub that will allow a user to insert an object (as an icon) into the selected cell. Generally these are Office or PDF files, but may also be images or text files.

The sub neatly formats the object to fit within the confines of the cell, and sets it to move/resize with the cell.

Pretty neat. User selects a target cell, and then clicks a form button that calls the macro. The standard file open dialog pops up, user selects the object, and clicks ok. Macro takes care of the rest.

OK. The problem:
If you do this manually, Excel knows how to find the proper icon for the target. PDF files get a PDF icon, word files get a word icon, etc. I just can't seem to figure out how to do that in code. If I use OLEObjects.Add and leave Iconfilename and iconindex blank I get an empty shape with no icon. The object is still there, and works ok ... just no icon.
In order to have an icon, I've resorted to using
[vba]
vFile = Application.GetOpenFilename("All Files,*.*", Title:="Select Artifact to insert")
If LCase(vFile) = "false" Then
Exit Sub
Else
lcl_a_fn = Split(vFile, "\")
lcl_vIconLabel = lcl_a_fn(UBound(lcl_a_fn))
End If
Set oNewObj = ActiveSheet.OLEObjects.Add(Filename:=vFile, _
Link:=False, DisplayAsIcon:=True, IconFileName:="explorer.exe", _
IconIndex:=1, IconLabel:=lcl_vIconLabel)
[/vba]

dows anyone know how I can determine the appropriate icon to use without knowing in advance what type of file will be selected?

 

[mikerickson]

Perhaps you could make a list of available icons and ask the user to choose from that list rather than having them navigate with GetOpenFilename.

 

[bubba2413]

they are not trying to select an icon, but insert a file object (as an icon). They know how to select a file. I think thats a basic windows skill. I can do the whole insert object thing for them, and make sure that the object is properly 'formatted' (i.e. sits neatly in a cell, and moves with that cell). The problem is wanting to make sure the icon matches the file type.

 

[mikerickson]

The code you have looks like its doing what you want it to.
Until the user selects the file to download, there is no way for you to know which icon is going to be needed and your "file selected" to "icon needed" code looks about as efficient as it can get.

 

[bubba2413]

Yup.. the code works. At the moment, I'm hard-coding the explorer.exe icon 1 in order to have an icon appear. That works. The problem is that it's the SAME icon for all file types.

The Help file for OLEObjects.Add indicates that IconFileName and IconIndex are optional. I take that to mean that unless I specify an icon, the default icon for the object would be used. But if you comment out those two parameters, you'll discover that the shape object is still created but without an icon. Interestingly, the Icon Label is also missing. The label is an absolute MUST-HAVE. The object does still work; and on the spreadsheet, you can rightclick and change the icon and label, but that kind of defeats the whole purpose...

So the question remains: How do I ensure that the 'proper' icon for the selected object is applied? (instead of one generic icon)

 

[shg]

Perhaps you could snag the file extension, and use it in a Switch function to select the icon in the IconFileName argument.

 

[bubba2413]

thought about that; and I suppose that is one possible workaround. Using the MacroRecorder, and examining the resultant macro, it seems there is some means for Excel to use the file extension to get the icon location from the registry. I was hoping one of the uber-gurus could show me how.

 

[bubba2413]

Bump... any uber-guru's out there?

 

[gijimbo]

Anybody ever get this figured out?

 

[mef1sto]

So in my case it works for a pdf file to add a date in the icons label but if i upload a .msg file it changes back and has only the filename stored, it doesn't keep the icon and label. Maybe it doens't read other file types, who knows?