2

I have created the below code for exporting an embedded Word document in a Excel Sheet to the workbook's path:

Private Sub Export()

Dim sh As Shape
Dim objWord As Object 'Word.Document
Dim objOLE As OLEObject

    Set sh = Sheet5.Shapes("Object 1")

    sh.OLEFormat.Activate

    Set objOLE = sh.OLEFormat.Object

    Set objWord = objOLE.Object

    objWord.Application.Visible = False

    objWord.SaveAs2 Filename:=ActiveWorkbook.Path & "\MyTemplate.docx", FileFormat:= _
    wdFormatDocumentDefault

    objWord.Application.Quit

End Sub

It is working perfectly on Excel 2013, but when I tried running it in my office on Excel 2007, it gave me a Run-time error '438' "Object doesn't support this property or method" at the line objWord.SaveAs2 Filename:=ActiveWorkbook.Path & "\MyTemplate.docx", FileFormat:= wdFormatDocumentDefault

Have tried playing around with defining objWord but with no luck. Any suggestions?

CaptainABC
  • 1,229
  • 6
  • 24
  • 40

1 Answers1

1

Use this one isntead:

objWord.SaveAs Filename:=ActiveWorkbook.Path & "\MyTemplate.docx", FileFormat:= _
    wdFormatDocumentDefault

Method SaveAs2 was introduced only in word 2010.

Also if you didn't add reference to word library in excel vba, you may want to change word constant wdFormatDocumentDefault to its value 16:

objWord.SaveAs Filename:=ActiveWorkbook.Path & "\MyTemplate.docx", FileFormat:=16
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • Yup that appears to have done it. Out of curiosity, is there any down side of using `SavaAs` as opposed to `SaveAs2`? – CaptainABC Apr 06 '14 at 08:52
  • 1
    actually thouse methods almost the same except: `Save2() takes an extra argument, CompatibilityMode.` see this post for details: http://stackoverflow.com/questions/4107210/saveas-vs-saveas2-in-the-microsoft-office-word-object-model – Dmitry Pavliv Apr 06 '14 at 08:54
  • 1
    Thanks for the clarifaction! I guess I'll stick to `SaveAs` – CaptainABC Apr 06 '14 at 09:00