Solved

Reading a Word 2010 document and writing to Excel 2010

Posted on 2013-11-11
2
419 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

627 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