Save an embedded Word document in an Excel spreadsheet to disk using VBA

Question

We have a Excel spreadsheet that currently generates a report using a Word template stored on the company LAN. This works fine for internal users but not for anyone not connected to the LAN e.g. laptop users.

Management does not want to distribute the template as a seperate file to external users but would prefer to embed this into the spreadsheet on a hidden worksheet. It is then proposed that when generating a report that the embedded template would then be saved to the users temp path and the report generated from there.

My question then, is how can i save the embedded Word template to disk using VBA. It would seem like a simple task but i haven't found a solution anywhere on google. Any help would be appreciated.

Solution

Okay, I think I may have an answer, but it is only tested in Excel 2010.

Sub SaveEmbedded()
Dim sh As Shape
Dim objWord As Object ''Word.Document
Dim objOLE As OLEObject

    ''The shape holding the object from 'Create from file'
    ''Object 2 is the name of the shape
    Set sh = ActiveSheet.Shapes("Object 2")

    ''Activate the contents of the object
    sh.OLEFormat.Activate

    ''The OLE Object contained
    Set objOLE = sh.OLEFormat.Object

    ''This is the bit that took time
    Set objWord = objOLE.Object

    ''Easy enough
    objWord.SaveAs2 Filename:="c:\docs\template.dot", FileFormat:= _
    wdFormatTemplate ''1=wdFormatTemplate
End Sub

Comments

[Paul]

Hi, thanks for the replies. The template is an OleObject. Presently it is a seperate file that sits on a network share. We have only decided to embed it into a seperate sheet because laptop users cant access the network share, so i dont have any code that access's the object yet. We are using Excel 2003 and the above code doesn't work. It fails on the saveas2 line with an error 'object doesn't support this property or method'. I've tried changing this to saveas but then I get 'application-defined or object-defined error'. I've also tried declaring objWord as Word.Document.

[Fionnuala]

SaveAs2 is the 2010 version, try SaveAs. I have an earlier version but I cannot test at the moment. Declaring objWord as Word.Document will only help you find properties through intellisense.

[Paul]

I've got the code to work. By accident I converted the object to a picture. Clicking on the object now shows =EMBED("Picture","") instead of =EMBED("Document","") in the formula bar. Using SaveAs it now works and the saved file is a Word template. Thanks again for your help.