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.

Answer 1

Check the MSDN Documentation.

I believe you are looking for the SaveAs Method.

There is an example of this here; MSDN - VBA Ref - SaveAs Method

Answer 2

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