Extracting an embedded ico file

[Dean76]

Hi,

I am currently working on a project where an excel file will create a folder, save itself as a hidden file and create a shortcut to itself within that folder. All easy enough. The one thing that I am trying to do is to use a custom icon for the shortcut file, however the main file will be sent to other users, who will run this 'setup' program to create the folder etc on their desktop and I am trying to find a way to include the icon file within the original excel workbook.
I had the idea of embedding the ico file within a worksheet, then extracting the embedded file into the new folder once created, however an intensive search on how to do this has left me coming up with blanks.

Hoping you guys can help me answer three main questions I have
1. Is this possible with VBA?
2. How can I do it!
and
3. Is there a better way?

Appreciate any assistance.

 

[daverunt]

I looked around for solutions and after a lot of searching I found this code. It uses a loop but doesn't matter if you only have 1 object.

vba - Extract OLE object data in Microsoft Office without OLE application - Stack Overflow

I tested it and it worked for me.
It copied an icon (license26.ico) embedded as a packager shell object (Insert -> Object -> Create from file) from my worksheet to a specified folder.
(Link to object/display as icon - neither selected)

If it doesn't work ---- I don't know : ).
vba - Shell.Application paste method does not create a file in the target folder - Stack Overflow

Sub Macro1()

For Each Shp In Sheet4.OLEObjects   '----------check worksheet
If InStr(1, Shp.Name, "Object", 1) Then 
Shp.Copy
' this code paste Embedded Object to folder
CreateObject("Shell.Application").Namespace("c:\junk\").Self.InvokeVerb "Paste" '------ check folder
End If
Next Shp

End Sub

 

[Dean76]

Thanks. Not working for me, keep coming up with 1004 Copy method of OLEObject class failed. I have found another way though, much more long winded! - Embedding and accessing a file in Excel with VBA and OLE Objects | by Danial Chowdhury | danny.fyi

Appreciate the assistance.

 

[daverunt]

Hi,

Yes I saw that one and avoided it...

Not sure why the object copy would fail.

Any Poster?

OLEObjects object (Excel)

Office VBA reference topic

docs.microsoft.com

If you are bored try adding the file with code see it you get the same issue when it tries to copy.

Sub Macro1()
Worksheets("Sheet4").OLEObjects.Add Filename:="c:\licence26.ico" '--- filepath

For Each Shp In Sheet4.OLEObjects   '----------check worksheet
If InStr(1, Shp.Name, "Object", 1) Then 
Shp.Copy
' this code paste Embedded Object to folder
CreateObject("Shell.Application").Namespace("c:\junk\").Self.InvokeVerb "Paste" '------ check folder
End If
Next Shp
End Sub

Either way glad you are sorted.