none
HOWTO: Embed an OLE Object into Excel and Link It with a File Using PowerShell RRS feed

  • Question

  • Howdy,

    I am trying to programmatically insert links into the Excel worksheet using PowerShell.

    I would like all cells in a specific column of a worksheet to be populated each with an object that links to a file. This object might be a HYPERLINK object or, possibly an Paint.Picture object that links to some, location, preferably on a file system.

    Currently, I thought  I could use OLE Embedding. For example, to insert a file icon from the C:\WINDOWS\system32\imageres.dll DLL, I'd do:

    $missing = [System.Type]::missing
    $excel = New-Object -Com Excel.Application
    $excel.visible = $True
    $wb = $excel.Workbooks.Add($missing)
    $ws = $wb.Worksheets.Item(1)
    $oleObjects = $ws.OLEObjects($missing)
    $FileIcon = $oleObjects.Add("Paint.Picture",$missing,$true,$True,"C:\WINDOWS\system32\imageres.dll",1,"",$missing,$missing,$missing)

    However, this only inserts an icon. How would I link the icon to a specific file? If that's not possible, how would I just insert a link to a file location in a cell?

    I'd like the user to be able to open referenced file when clicking on an icon or link in a specific cell. For example, I could use the PDF file type icon to reference a User manual in a cell.

    Is that possible?

    Thank you in advance.


    Well this is the world we live in And these are the hands we're given...

    • Moved by Bill_Stewart Tuesday, January 27, 2015 4:08 PM Move to more appropriate forum
    Tuesday, January 27, 2015 3:34 PM

All replies

  • Hey Bill,

    Why did you move the topic? How is it relevant to Excel? It's more about scripting with PowerShell that about using Excel!


    Well this is the world we live in And these are the hands we're given...

    Tuesday, January 27, 2015 5:55 PM
  • Suggestion.  Add all object to a template and create tehnew workbook from the template.

    The add you are usingjust adds an icon fromm teh iamge resource file.  The oleobject.add method can add any type of file or ressouce based object.  It you are trying to link a file you need to add a hyperlink to the graphic you have just inserted.

    This is a little more straight forward.

    $missing=[System.Type]::missing
    $excel = New-Object -Com Excel.Application
    $excel.visible = $True
    $wb=$excel.Workbooks.Add()
    $ws=$wb.Worksheets.Item(1)
    $FileIcon=$ws.OLEObjects().Add('Paint.Picture',$missing,$true,$True,'C:\WINDOWS\system32\imageres.dll',1,'',$missing,$missing,$missing)
    


    ¯\_(ツ)_/¯

    Tuesday, January 27, 2015 8:16 PM
  • Here are the docs.  REad them carefully: https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.oleobjects.add(v=office.15).aspx

    You asked for an icon to be displayed.  ;Paint.Picture' is the class used to link it.  Double cliccking the icon will open "MSPaint".


    ¯\_(ツ)_/¯

    Tuesday, January 27, 2015 8:22 PM
  • /*
    Here are the docs.  REad them carefully:  https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.oleobjects.add(v=office.15).aspx

    You asked for an icon to be displayed.  ;Paint.Picture' is the class used to link it.  Double cliccking the icon will open "MSPaint".

    ¯\_(ツ)_/¯ */


    Hi,

    Thanks for the tips, but I think I was kind of vague on properly describing the task and the problem that I want to solve.

    The sample snippet I posed was just to demonstrate that I sort of understood how to add MSPaint OLE object into Excel workbook, but can't get how do I add another type of objects.

    As far as I understand the concept of this dated technology, inserted OLE object is handled by its native OLE server. Surely, the MSPaint OLE object is handled by Paint.

    What I wanted is the MSPaint object's server to call another server that would handle Hyperlinks. In a sense that I want my user to be able to download document/application by the link embedded in Excel worksheet. I don't know if this could be implemented.

    If not, how would I insert a Hyperlink object if there is such a type of objects? Also how would I enumerate OLE objects other than by using Oleviewer?

    Thank you.


    Well this is the world we live in And these are the hands we're given...


    Thursday, January 29, 2015 10:12 PM
  • YOu can add certain objects but you have to program them.  If I add Word.Applicaiton I will get an icon which opens word or I can use VBA to open it with a diocumment. \

    OR

    I can skip the OLE object and give a filename like mydoc.docx and it will embed my document whihc will open when the icon is clicked.

    Research OLE to seehow it works.  I warn you, it is not for the non-programmer.  It is one of thee hardest bits of WIndow technology for most to progrqm but is easy to use when in a document.


    ¯\_(ツ)_/¯

    Thursday, January 29, 2015 11:22 PM
  • Hi,

    >>If I add Word.Applicaiton I will get an icon which opens word or I can use VBA to open it with a diocumment

    Sure. OK, never mind. Thank you for your help.

    >>I can skip the OLE object and give a filename like mydoc.docx and it will embed my document whihc will open when the icon is clicked.

    Yeah,  but I didn't want them embedded, just linked.

    Do you know if Oleview is still available for downloading? It's been packaged with VS than with Windows 2000 Resource Kit, but I don't have any at hand. Possibly some WDK contains it at least as a source code?

    Thanks 


    Well this is the world we live in And these are the hands we're given...

    Thursday, January 29, 2015 11:33 PM
  • Do you know if Oleview is still available for downloading? It's been packaged with VS than with Windows 2000 Resource Kit, but I don't have any

    IT iss still in the SDk as far as I know.  It is in my copy of the Win 7.1 SDK.  It is also built into any other products including Office products.

    In Word/Excel/Outlook etc just open the VBA editor and look on the View menu or hit the F2 key.


    ¯\_(ツ)_/¯

    Friday, January 30, 2015 12:00 AM