Microsoft Office Forums - View Single Post - Extract data form one Excel file to another

Guessed · 06-07-2023, 04:27 PM

Greg

I did a google search and found some relevant code by Andy Pope which works in Word to read or write to its own embedded spreadsheet.

Code:

Sub WriteToSS()
  'Sourced from...
  'https://social.msdn.microsoft.com/Forums/en-US/c4969934-0a4a-4e2c-bb56-cf05f756dc82/can-you-use-vba-to-access-a-spread-sheet-embedded-in-a-word-doc?forum=isvvba
  ' added a couple of lines to read cell values and save any changes
  Dim objSS As InlineShape
  With ActiveDocument.InlineShapes(1)
    .OLEFormat.DoVerb wdOLEVerbHide
    With .OLEFormat.Object.Application
        With .Workbooks(1).Worksheets(1)
          Debug.Print .Cells(1, 1).Value, .Range("B1").Value  'reading cells
          .Cells(1, 1).Value = "Hello"  'writing to a cell
          .Range("B1").Value = "Big Boy"    'writing to another cell
        End With
        .Workbooks(1).Save  'if you wanted to save the changed cell values
        .Quit
    End With
  End With
End Sub

If you are already running code from Excel to access that Word document then you've got that part of the automation running and can adapt the relevant lines above into your existing code.
Getting the right InlineShape might be a bit tricky and relying on its numerical position in the document is problematic so I would probably use Format Object to set its Alt Text to a unique value so the code could iterate through all the InlineShapes to find the one which has that Alt Text value. An example of looping through a docs Shapes (same for InlineShapes) is shown in this thread.