Storing data from a worksheet embedded in MS Word

Question

I am new to this board. I have searched through past answers to various questions related to mine but have not succeeded in finding a solution to my problem.

What I need to do is: 1) Using vba/vb within an Excel doc, locate an Excel spreadsheet which is embedded in a Word doc. 2) Read data from its cells. 3) Store that data in the cells of the current Excel doc.

I have been able to locate the embedded Excel spreadsheet but can't see how to get it's data into the current spreadsheet's cells. Here's the code by which I have located the embedded spreadsheet:

Sub GetWordata()

  Dim objWord As Object
  Set objWord = CreateObject("Word.Application")
  Dim singleLine As Paragraph
  Dim lineText As String
  Dim word_app As word.Application
  Dim wDoc As word.Document
  Dim r As Integer
  Dim i As Long, Rng As Range
  Dim Evalue As String
.
.
.
  With activeDocument
    For i = .InlineShapes.Count To 1 Step -1
      With .InlineShapes(i)
        If Not .OLEFormat Is Nothing Then
          If Split(.OLEFormat.ClassType, ".")(0) = "Excel" Then
            MsgBox "Excel File"
          End If
        End If
      End With
    Next
  End With

The message box in the code above displays, indicating that I have located the embedded spreadsheet.

Thanks ahead for any help.

Try some formatting, the question is unreadable in this way. — Bert Verhees, Oct 10 '18 at 18:04
[This](https://stackoverflow.com/questions/483813/modify-embedded-excel-workbook-in-word-document-via-vba) could get you started — cybernetic.nomad, Oct 10 '18 at 18:24
Why are you getting data from a word-embedded Excel file, and putting it into another file? Maybe you could do the reverse? Or maybe your time could be better spend replacing this solution with something more robust that doesn't rely on Office documents and VBA scripts. — EdHayes3, Oct 10 '18 at 18:40
Thanks for replies everyone. For replies to some of the posts please see below. I've been trying some of the code examples that were referenced, but they refer to the active document, which is not the doc I need to access. Rather, from within the active document, (vba code written in an excel spreadsheet), I need to access a spreadsheet that's embedded inside a word doc. Any additional help appreciated. Ok, on to answers to above replies:Sorry about formatting. I just typed it in and hit "enter" at the end of each line will do better next time. As — holdemfoldem081, Oct 16 '18 at 15:47
to why I'm having to process data from a spreadsheet that's embedded in a word doc in the first place, I'm stuck with it cuz that's the way our dept is going to be given the data and we just have to live with it. :( Anyway thanks again all and any help appreciated. — holdemfoldem081, Oct 16 '18 at 15:50

score 0 · Answer 1 · answered Oct 27 '18 at 16:35

This will import data from all tables in all Word files in a folder. This is just a small sample of what can be done with COM for Word and Excel (run the code from Excel).

Sub WordToExcel()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim x As Integer
Dim strFilename As String
Dim strFolder As String
Dim temp As String

Set wdApp = New Word.Application
'initialise counter
x = 1
'search for first file in directory
strFolder = "C:\test\"
strFilename = Dir(strFolder & "*.doc")
'amemd folder name
Do While strFilename <> ""
Set wdDoc = wdApp.Documents.Open(strFolder & strFilename)
temp = wdDoc.Tables(1).Cell(2, 1).Range.Text 'read word cell
Range("A2").Offset(x, 0) = temp
temp = wdDoc.Tables(1).Cell(2, 2).Range.Text 'read word cell
Range("A2").Offset(x, 1) = temp
'etc
temp = wdDoc.Tables(1).Cell(2, 3).Range.Text 'read word cell
Range("A2").Offset(x, 2) = temp
temp = wdDoc.Tables(1).Cell(2, 4).Range.Text 'read word cell
Range("A2").Offset(x, 3) = temp

wdDoc.Close
x = x + 1
strFilename = Dir
Loop
wdApp.Quit
Set wdDoc = Nothing
Set wdApp = Nothing
End Sub

Storing data from a worksheet embedded in MS Word

1 Answers1