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

Question

We have an Excel spreadsheet that is currently generating a report using a Word template stored on the company's local network. This is great for internal users, but not for those who are not connected to the local network, for example. laptop users.

Management does not want to distribute the template as a separate file for external users, but prefers to insert it into a spreadsheet on a hidden sheet. Then it was proposed that when generating the report, the built-in template would then be saved in the user pace path and a report would be generated from it.

My question is: how can I save the built-in Word template to disk using VBA. It would seem to be a simple task, but I did not find a solution anywhere on Google. Any help would be appreciated.

Answer 1

Ok, I think I might 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

Answer 2

Check the MSDN documentation.

I believe you are looking for the SaveAs method.

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