Solved

Reading a Word 2010 document and writing to Excel 2010

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

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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.

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now