Excel VBA: Coding issues for Looping and Adding rows with Titles

[llenza]

Note: The original document is in Word all cell are tables except titles, which are listed two rows above the table (just so you know where everything is place). User Case ID is always the same size (cols and rows) and the Basic Flow varies in size - same cols, but it can have less or more rows. That is why it was best to have VBA recordnize tables and pull them from MS Word 2007.

[h=1]User Case ID 1: New Client[/h]

[h=1]Overview[/h]	ColB, Row3
Pre Cond	ColB, Row4
Post Cond	ColB, Row5
Assumptions	ColB, Row6
Requirements	ColB, Row7
Actors	ColB, Row8

<tbody>
</tbody>

Basic Flow:

Step	Desc	Actor	Comments	Screen
1	ColB, Row12	ColC, Row12	ColD, Row12	ColE, Row12
2	ColB, Row	ColC, Row	ColD, Row	ColE, Row
3	ColB, Row	ColC, Row	ColD, Row	ColE, Row
4	ColB, Row	ColC, Row	ColD, Row	ColE, Row
5	ColB, Row	ColC, Row	ColD, Row	ColE, Row

<tbody>
</tbody>

[h=1]User Case ID 2: Old Client[/h]

[h=1]Overview[/h]	ColB, Row19
Pre Cond	ColB, Row20
Post Cond	ColB, Row21
Assumptions	ColB, Row22
Requirements	ColB, Row23
Actors	ColB, Row24

<tbody>
</tbody>

Basic Flow:

Step	Desc	Actor	Comments	Screen
1	ColB, Row30	ColC, Row30	ColD, Row30	ColE, Row30
2	ColB, Row	ColC, Row	ColD, Row	ColE, Row
3	ColB, Row	ColC, Row	ColD, Row	ColE, Row

<tbody>
</tbody>

[h=1]User Case ID 3: Existing Client[/h]

[h=1]Overview[/h]	ColB, Row40
Pre Cond	ColB, Row41
Post Cond	ColB, Row42
Assumptions	ColB, Row43
Requirements	ColB, Row44
Actors	ColB, Row45

<tbody>
</tbody>

Basic Flow:

Step	Desc	Actor	Comments	Screen
1	ColB, Row51	ColC, Row51	ColD, Row51	ColE, Row51
2	ColB, Row	ColC, Row	ColD, Row	ColE, Row
3	ColB, Row	ColC, Row	ColD, Row	ColE, Row
4	ColB, Row	ColC, Row	ColD, Row	ColE, Row
5	ColB, Row	ColC, Row	ColD, Row	ColE, Row
6	ColB, Row	ColC, Row	ColD, Row	ColE, Row

<tbody>
</tbody>

My MS Word 2007 doc has 133 tables, which need to get transferred into Excel. I have borrowed parts and pieces of VBA code to pull from Word these tables and copy them to Excel. Unfortunately, it only copies one (this is the main problem). I have tried including a Loop, but guess I am placing it in the wrong order or place.

VBA Code:
1. VBA ask on which Word document to perform the operation (Word Open menu appears)
2. Then it Counts how many tables the document contains in my case it's 133 in the example provided above it should say SIX (3 Use Cases/3 Basic Flows).
3. It asks, which table do I want to copy over (it only pull one at a time)

All the code above works fine! (Steps 1 - 3)

Wish list (what I have been trying to get it to do with 2 weeks now):
1. I want for it to pull all the table from where I tell it to START
2. Add the Use Case and Basic Flow titles to Excel (located two rows above the tables in Word)

Issues:
1. Can’t write the code for looping from Start Number to Finish (133rd table)
2. Can’t add rows to include Titles ready appearing on Word or adding them to the VBA code

VBA Codes:

Sub ImportWordTable()
Dim wdDoc As Object
Dim wdFileName As Variant
Dim TableNo As integer ‘table number in Word
Dim iRow As integer ‘row index in Excel
Dim iCol As integer ‘row index in Excel
Dim Counter As integer ‘row index in Excel
Dim myNum As integer ‘row index in Excel

WdFileName = Application.GetOpenFilename(“Word files (*.docx,*docx, , _”Browse for file containing table to be imported”)

If wdFileName = False Then Exit Sub ‘(user cancelled import file browser)

Set wdDoc = GetObject(wdFileName) ‘open Word file
With wdDoc
TableNo = wdDoc.tables.Count
If TableNo = 1 Then
MsgBox “This document contains no tables”, vbExclamation, “Import Word Table”

ElseIf TableNo > 1 Then
TableNo = InputBox (“This Word document contains “ & TableNo & “tables.” & vbCrLf & _”Enter table number from where to begin table import”, “Import Word Table”, “1”)

End If
With .tables(TableNo)

‘copy cell contents from Word table cells to Excel cells
For iRow = 1 To .Rows.Count
For iCol = 1 To .Columns.Count
Cells(iRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
Next iCol
Next iRow

End With

End With

Set wdDoc = Nothing

End Sub

 

[b.downey]

The following modification to you code will loop thru the remaining tables in the word file.

Option Explicit
Sub ImportWordTable()
    Dim wdDoc As Word.Document
    
    Dim wdFileName As Variant
    Dim TableNo As Integer 'table number in Word
    Dim FirstTableNo As Integer
    
    Dim iRow As Integer 'row index in Excel
    Dim iCol As Integer 'row index in Excel
    Dim xlRowOffset As Long
    
    Dim Counter As Integer 'row index in Excel
    Dim myNum As Integer 'row index in Excel
    
    Dim WdTable As Word.Table
    
    wdFileName = Application.GetOpenFilename("Word files (*.docx),*docx", , "Browse for file containing table to be imported")
    
    If wdFileName = False Then Exit Sub '(user cancelled import file browser)
     
    Set wdDoc = GetObject(wdFileName) 'open Word file
    TableNo = wdDoc.Tables.Count
    If TableNo = 1 Then
        MsgBox "This document contains no tables", vbExclamation, "Import Word Table"
        Exit Sub
    ElseIf TableNo > 1 Then
        FirstTableNo = InputBox("This Word document contains " & TableNo & "tables." & vbCrLf & "Enter table number from where to begin table import", "Import Word Table", "1")
    End If
    
    xlRowOffset = 1
    For TableNo = FirstTableNo To TableNo
        With wdDoc.Tables(TableNo)
            'copy cell contents from Word table cells to Excel cells
            For iRow = 1 To .Rows.Count
                For iCol = 1 To .Columns.Count
                    Cells(xlRowOffset, iCol) = WorksheetFunction.Clean(.Cell(iRow, iCol).Range.Text)
                Next iCol
                xlRowOffset = xlRowOffset + 1
            Next iRow
        End With
    Next TableNo
    Set wdDoc = Nothing
End Sub