VBA Word表副本丢失数据 [英] VBA word table copy loses data

问题描述

我继承了一些VBA代码，该代码将Word文档中表的内容复制到Excel.该代码本身来自一个Excel插件，该插件使用后期绑定来调用Word，以避免在旧版本的Excel中使用时出现引用错误.加载项本身在Office 2016中维护，同时还在Office 2016、2013和2010中使用.

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.

该程序的核心如下:

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

不，由于无法进行从Word到Excel的复制粘贴，它无法进行任何处理，不能很好地处理从单元格到单元格的文本复制，不能很好地处理单元格中断，也不能处理内容控件.

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.

当该过程从Word复制大量大表时，我已经观察到一个问题，它将丢失一个表.但是，当我通过在调试器中强制停止或在循环中添加Application.Wait来减慢该过程时，问题就消失了.

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.

代码执行以下操作:

• 对于文档，请遍历文档中的所有表
• 然后对于一个表，循环遍历该表中的所有单元格，然后将其复制到Excel中，以保留背景色和前景色

一个典型的文档可能有10到20个表格，每个表格有50个或更多的单元格.

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

几乎遍历表时，如果VBA仍然很忙，则后续表将返回空.

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

我尝试了以下操作:

• 使用早期绑定更改为Word自动化
• 摆脱ActiveSheet.Cells(...).Activate并改用Cells(y, x)
• 使用计数器并在循环结束时使用set tbl = doc.tables(tc)和set tbl = Nothing进行循环
• 多个DoEvents
• 只需设置单元格的文本即可，循环中的工作量最少(这里丢失的数据更少)

• 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)

行为没有改变.简直诡reach. ужас.

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

有没有没有Application.Wait或sleep的更好的方法?在下一次迭代开始之前，如何确定Excel是否已完成?

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?

推荐答案

请尝试一下:

更改:

For Each tbl In doc.Tables
    prev = -42

收件人:

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

有一个类似的问题:使用VBA，我打开了一个包含> 300页和> 200个表的Word文档.当代码在文档中循环时，它从表中收集数据.我将.Select行用于调试目的，所以我知道文档在哪里工作，但是在调试后稍后注释掉它时，发现该代码可以运行，但不会击中每个表.

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.

我还尝试添加一个Wait循环，以确保在运行我的代码之前完全加载文档，但这没有帮助.

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

您还可以在代码顶部附近添加一行Application.ScreenUpdating = False，以避免屏幕闪烁.Select原因.

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表副本丢失数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！