Solved

Reading a Word 2010 document and writing to Excel 2010

Posted on 2013-11-11
2
415 Views
Last Modified: 2013-11-12
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.
0
Comment
Question by:jmohsin
2 Comments
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 225 total points
ID: 39640271
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

    ActiveSheet.Range("A:AZ").ClearContents

    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

0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 275 total points
ID: 39640286
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

0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question