Open Excel File embedded in Word Document from vba in Excel

[JoeStellar]

As the above title suggests I'm tring to open an Excel document that is embedded in a Word document from within my Excel document using VBA. This is the first time I couldn't find a pre-existing answer to my problem.

The reason I'm trying to do this, in case someone has a better solution, is because our official test specification is a Word Document and embedded in this word document is an Excel document that contains the exact data I need to compare data that I have collected to. The test specification will be updated from time to time, so I can't just save the embedded Excel file locally and use it as a reference. I have my whole process automated and just have this final step to complete.

 

[Macropod]

If it's just data, why not use a Word table? Alternatively, you could have your Excel data in a separate file that your document links to, thus being able to display the data in Word whilst having the flexibility of not having to open the Word document for your analyses.

 

[ZVI]

Hi,
Use this code as a template to edit sheet of Excel's workbook which is embedded into a Word document:

Sub EditWbEmbeddedIntoDocument()
 
  ' Change to suit
  Const MyDoc = "D:\Tmp\MyDocument.docx"
 
  ' Variables
  Dim objWord As Object, objDoc As Object
  Dim objWb As Workbook
 
  ' Get Word appplication
  On Error Resume Next
  Set objWord = GetObject(, "word.application")
  If Err Then
    Set objWord = CreateObject("word.application")
    objWord.Visible = True
  End If
  On Error GoTo 0
 
  ' Get Word Document
  Set objDoc = objWord.Documents.Open(MyDoc)
 
  ' Get Workbook
  With objDoc.InlineShapes(1).OLEFormat
    .Activate
    Set objWb = .Object
  End With
 
  ' Change Sheets(1)
  objWb.Windows(1).WindowState = xlMaximized
  objWb.Sheets(1).Range("A2") = Now
 
End Sub

Regards