VBA word table copy loses data

Question

I've inherited some VBA code which copies the contents of tables in a Word document to Excel. The code itself is from an Excel addin which invokes Word using late binding to avoid the reference error when used in older versions of Excel. The addin itself is maintained in Office 2016 while also being used in Office 2016, 2013, and 2010.

The heart of the program is the following:

tc = 1                                                     ' table counter
For Each tbl In doc.Tables
    prev = -42                                             ' previous row

    Application.Wait DateAdd("s", 2, Now)                  ' Note this line

    For Each cel In tbl.Range.Cells
        cont = CellContents(cel.Range)                     ' dim cont() as string
        txt = cont(0)
        xx = cel.ColumnIndex
        yy = cel.RowIndex
        If yy <> prev Then
            xtra = 1                                       ' extra x cell counter
            prev = yy                                      ' reset for new row
        End If

        xtra = xtra - 1
        For Each v In cont                                 ' dim v as variant
            txt = CStr(v)
            ActiveSheet.Cells(xlrow + yy, xtra + xx).Activate
            ActiveCell = txt
            ActiveCell.Font.Bold = cel.Range.Bold
            colr = cel.Range.Font.Color
            ActiveCell.Font.Color = colr
            colr = cel.Shading.BackgroundPatternColor
            If colr <> -16777216 Then ActiveCell.Interior.Color = colr
            Select Case cel.Range.ParagraphFormat.Alignment
                Case 2 ' wdAlignParagraphRight
                    ActiveCell.HorizontalAlignment = xlRight
                Case 1 ' wdAlignParagraphCenter
                    ActiveCell.HorizontalAlignment = xlCenter
                Case 0, 3 ' wdAlignParagraphLeft, wdAlignParagraphJustify
                    ActiveCell.HorizontalAlignment = xlLeft
                Case Else
            End Select
            xtra = xtra + 1
        Next v
    Next cel
    xlrow = xlrow + tbl.rows.Count + 1
    Application.StatusBar = "Table " & tc & " in " & nm
    DoEvents
    tc = tc + 1
Next tbl

No, copy paste from Word to Excel won't do as it does not do any processing, does not handle text copy from cell to cell well, does not handle cell breaks well, nor does it handle content controls.

I've observed a problem when this procedure is copying a large number of large tables from Word, it will miss a table. However, when I slow down the process, either by forcing a stop in the debugger or adding an Application.Wait in the loop, the problem disappears.

The code does the following:

For a document, loop over all tables in the document
Then for a table, loop over all cells in that table and copy them over to Excel, preserving the background and foreground colours

A typical document may have 10 to 20 tables with 50 or more cells each.

It's almost as if when iterating through the tables, if VBA is still busy, subsequent tables are returned empty.

I have tried the following:

Changing to Word Automation using Early Binding
Getting rid of the ActiveSheet.Cells(...).Activate thing and using Cells(y, x) instead
For loop using counter and set tbl = doc.tables(tc) and set tbl = Nothing at the end of the loop
Multiple DoEvents
Just setting the cell's text and nothing else, minimal work in the loop (Here I lost less data)

There was no change to the behaviour. Simply treacherous. ужас.

Is there a better way to do this, without Application.Wait or sleep? How to determine that Excel is really done before starting on the next iteration?

there are some missing part of your code. Like: what is v and cont in internal loop? If you have word table well formatted there should not be any problem with Copy-Paste solution. Moreover, how did you set binding between Word and Excel? — Kazimierz Jawor
CellContents() picks apart content controls and returns an array of strings. — V. V. Kozlov

Rick2302 Rick2302 · Accepted Answer · 2017-12-13T22:11:03

Please give this a try:

Change:

For Each tbl In doc.Tables
    prev = -42

To:

For Each tbl In doc.Tables
    tbl.Select ' < add this
    prev = -42

Had a similar issue: Using VBA, I open a Word Document with > 300 pages and > 200 tables. While the code loops through the document, it collects data from the tables. I used the .Select line for debugging purposes so I knew where in the Document the code was working, but found when I commented it out later after debugging, the code would run, but not hit every table.

I also tried adding a Wait loop, to ensure the Document fully loaded prior to running my code, but that did not help.

You can also add a line Application.ScreenUpdating = False near the top of your code to avoid the screen flash .Select causes.

This does not really answer your question, but perhaps it will get your code working.

VBA word table copy loses data

1 Answers