Extract data form one Excel file to another

Guessed · #2 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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Extract data from a Word file.	donlincolnmsof	Word VBA	3	08-31-2019 05:27 AM
Extract data from HTML File.	donlincolnmsof	Word VBA	5	08-26-2019 08:36 PM
Extract data from HTML File.	donlincolnmsof	Word VBA	0	03-07-2019 12:17 PM
Outlook 2013 Forms (how to questions)- Quick opening of a form file & Linking form's data to Excel	gamin2407	Outlook	0	01-21-2017 10:14 PM
Macro to highlight repeated words in word file and extract into excel file	aabri	Word VBA	1	06-14-2015 07:20 AM