CODEWITHSUNDEEP
vbaexcelforeachms-word
fvuong
fvuong

Reputation: 167

Can excel macro loop through each page of a Word doc to get each table?

I have a Word doc that contains many tables.

I need to export the tables into excel and also add a column beside each row to show the corresponding Word page number. This excel file will be used to make an index for my Word doc once it is ready to be printed.

I am able to access my Word file with a macro within my excel file. I would like to loop through each page in Word, then loop through each table within the page to copy into excel. This way I will know what page I am on and can get the page number.

So I would like to do something like this:

for each page in word
    for each table in this page
        copy the table into excel and also add the Word page number beside each row
    end
end

I've tried writing the macro but it can not get any tables within a page:

Set wdDoc = GetObject(wdFileName) 'open Word file

With wdDoc
    pagesTot = wdDoc.Pages.Count
    tableTot = wdDoc.Tables.Count

    resultRow = 4

    For tableStart = 1 To tableTot
        With .Tables(tableStart)
            'copy cell contents from Word table cells to Excel cells
            For iRow = 1 To .Rows.Count
                For iCol = 1 To 2 'only need to copy first 2 columns from Word into Excel
                    'This does not get the Word page number
                    TblPage = .cell(iRow, iCol).Information(3)
                    'copy cells from Word to Excel
                    Cells(resultRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
                    'put the Word page number beside the row in Excel
                    Cells(resultRow, iCol).Offset(0, 1).Value = TblPage
                Next iCol
                resultRow = resultRow + 1
            Next iRow
        End With
        resultRow = resultRow + 1
    Next tableStart
End With

However, when I am inside the page loop, I cannot seem to access tables within that page. I've tried using a MsgBox to show the number of tables in that page, but it always shows 0.

So is it even possible to access tables within a page?

Any help is much appreciated, thanks.

EDIT: I edited the above code and I'm getting closer. The only line that I am having trouble with is this:

TblPage = .cell(iRow, iCol).Information(3)

I'm trying to use that line to get the page number from Word but it ends up getting 0's for page number. Again, I am writing all of this code in Excel.

Does anyone know the proper way to get the page number from Word?

Thanks again.

Upvotes: 0

Views: 2588

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19077

Your suggested logic is quite close of what you need. In fact, the best is to loop through tables and check on which page is (the end of) the table. Here is the logic you need.

Sub GetPageNumberForTable()

    Dim TBL As Table
    Dim TblPage As Integer
    For Each TBL In ActiveDocument.Tables

        'this is value you need
        TblPage = TBL.Range.Information(wdActiveEndPageNumber)

        'test- check value in immediate window
        Debug.Print TblPage

        'your copy code here
        '.....
    Next

End Sub

Upvotes: 1

Related Questions