• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • Last Modified:

Reading a Word 2010 document and writing to Excel 2010

1. I have a very large Word 2010 document that contains a series of tables.

2. I want to scan all the cells in ALL tables in the Word 2010 document named "MyInput.docx" and if a cell contains the string "MyString", I want to write ALL contents from that cell to column A in a new / empty Excel 2010 worksheet which resides in MyOut.xlsx.

3. When the script ends, I will have MyOut.xlsx with all the matching cells in column A.
2 Solutions
Give this a try, (I haven't had a chance to test it.

Option Explicit

Sub ImportSelectedTextFromWordTables()

    Dim strTextSought As String
    Dim wdDoc As Object
    Dim wdFileName As Variant
    Dim intTableNo As Integer
    Dim intRow As Integer
    Dim intCol As Integer
    Dim intResultRow As Integer
    Dim intTableStart As Integer
    Dim intTableTot As Integer

    On Error Resume Next


    wdFileName = Application.GetOpenFilename("Word files (*.doc*),*.doc*", , _
                                             "Browse for file containing table to be imported")
    strTextSought = InputBox("Enter text to extract.", "Dump String")

    If wdFileName = False Then Exit Sub

    Set wdDoc = GetObject(wdFileName)

    With wdDoc
        intTableNo = wdDoc.tables.Count
        intTableTot = wdDoc.tables.Count
        If intTableNo < 1 Then
            MsgBox "This document contains no tables", _
                   vbExclamation, "Import Word Table"
        End If

        intResultRow = 4

        For intTableStart = 1 To intTableTot
            With .tables(intTableStart)
                For intRow = 1 To .Rows.Count
                    For intCol = 1 To .Columns.Count
                        If WorksheetFunction.Clean(.cell(intRow, intCol).Range.Text) = strTextSought Then
                            Cells(intResultRow, intCol) = WorksheetFunction.Clean(.cell(intRow, intCol).Range.Text)
                        End If
                    Next intCol
                    intResultRow = intResultRow + 1
                Next intRow
            End With
            intResultRow = intResultRow + 1
        Next intTableStart
    End With

End Sub

Open in new window

Here is some Word macro code that will do something like that. It uses early binding, so you will need to set a reference to the Microsoft Excel Object Library.
Sub ToExcel()
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWks As Excel.Worksheet
    Dim cl As Word.Cell
    Dim tbl As Table
    Dim r As Long
    Dim doc As Document
    Dim strText As String
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlWbk = xlApp.Workbooks.Add
    Set xlWks = xlWbk.Worksheets(1)
    'Set doc = ActiveDocument
    Set doc = Documents.Open("C:\MyFolder\MyInput.docx")

    For Each tbl In doc.Tables
        For Each cl In tbl.Range.Cells
            strText = GetCellText(cl)
            If InStr(strText, "MyString") Then
                r = r + 1
                xlWks.Cells(r, 1).Value = strText
            End If
        Next cl
    Next tbl

End Sub

Function GetCellText(cl As Cell) As String
    Dim rng As Range
    Set rng = cl.Range
    rng.MoveEnd wdCharacter, -1
    GetCellText = rng.Text
End Function

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now