通过VBA将Word表单转换为Excel需要很长时间

Question

我有一个Word表单，大约有150个可填充元素。我用以下代码将此表单导入Excel，在VBA中选择文件作为myDoc后:

With myDoc
For Each CCtl In .ContentControls
i = i + 1
j = 1
Tags = Split(CCtl.Tag, ";")
For x = 0 To UBound(Tags)
myWkSht.Cells(i, j + x) = Tags(x)
Next x
myWkSht.Cells(i, j + 5) = CCtl.Range.Text
myWkSht.Cells(i, j + 6) = BieterID
Next
myWkSht.Columns.AutoFit
End With

代码做了它应该做的事情，但是宏需要半个多小时才能完成。有人知道如何优化这里的运行时吗?

Answer 1

您可以使用以下代码

它首先将数据读入数组-不拆分标记!然后将此数组写入Excel工作表。

根据您的Excel版本，您可以使用TEXTSPLIT公式或逐行分割。

我认为这比你的解决方案快多了。

Dim arrValues As Variant, i As Long
ReDim arrValues(1 To myDoc.ContentControls.Count, 1 To 3)
Dim cc As ContentControl
For Each cc In myDoc.ContentControls
i = i + 1
arrValues(i, 1) = BieterID
arrValues(i, 2) = cc.Range.Text
arrValues(i, 3) = cc.Tag
Next

Dim rgFormula As Range
Dim arrSplit As Variant
With myWkSht
.Cells(1, 1).Resize(UBound(arrValues, 1), UBound(arrValues, 2)).Value = arrValues

For i = 1 To UBound(arrValues, 1)

'  Solution for Excel 365 current channel
'        With .Cells(i, 4)
'            .Formula2R1C1 = "=TEXTSPLIT(RC[-1],"";"")"
'            .SpillingToRange.Value = .SpillingToRange.Value
'        End With

'Solution for all other Excel versions
arrSplit = Split(.Cells(i, 3), ";")
.Cells(i, 4).Resize(, UBound(arrSplit) + 1) = arrSplit
Next

.Columns(3).Delete xlShiftToLeft
End With