Solved

Reading a Word 2010 document and writing to Excel 2010

Posted on 2013-11-11
2
416 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

828 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