如何在维护表格公式的同时使用 VBA 更新 Excel 表格

How to update Excel Table using VBA while maintaining table's formulas

提问by user2286596

I have a spreadsheet that defines several Excel tables which include both source data and functions using the source data. For example, a table named MyTbl consists of three "source columns" and one formula column ...

我有一个电子表格，它定义了几个 Excel 表，其中包括源数据和使用源数据的函数。例如，名为 MyTbl 的表由三个“源列”和一个公式列组成......

Part   Price  Qty  Extended Price
------ -----  ---  --------------
Shoe   5.50   10   =(MyTbl[[#ThisRow],[Price]])*(MyTbl[[#ThisRow],[Qty]])
...

Periodically, the source columns must be replaced by values from a CSV file, but the formula column(s) should remained unaffected. The file is something like:

源列必须定期替换为 CSV 文件中的值，但公式列应不受影响。该文件类似于：

Shoe,5.65,98
Coat,12.65,223
...

How do I "refresh" the source columns while maintaining the existence of the Excel Table and its formulas? I'd like to do this in VBA as the CSV file does not have a consistent name or path.

如何在保持 Excel 表格及其公式存在的同时“刷新”源列？我想在 VBA 中执行此操作，因为 CSV 文件没有一致的名称或路径。

Thanks in advance.

提前致谢。

采纳答案by user2286596

Thanks to Doug Glancy for his suggestions.

感谢道格·格兰西的建议。

The original question dealt with the loading of a .csv into a listobject. I ended up loading the data from the csv into a listobject on a new sheet. I was then able to merge a source listobject into a target listobject while maintaining the additional columns (and formulas) the target maintained using the procedure below. This procedure clears the data that is not needed, removes rows if the source contains less rows than the target, copies the data, and resizes the listobject.

最初的问题涉及将 .csv 加载到列表对象中。我最终将 csv 中的数据加载到新工作表上的列表对象中。然后，我能够将源列表对象合并到目标列表对象中，同时维护目标使用以下过程维护的附加列（和公式）。此过程清除不需要的数据，如果源包含的行少于目标，则删除行，复制数据并调整列表对象的大小。

Assumptions within this solution
- source list object contains some number of columns less than the target
- both list objects contain a single header row (I did not test anything more than a single header row)

此解决方案中的假设
- 源列表对象包含的列数少于目标
- 两个列表对象都包含一个标题行（我没有测试超过一个标题行的任何内容）

Private Sub CopyTableData(loSource As Excel.ListObject, loTarget As Excel.ListObject)
Dim lSourceRowCount As Long

Private Sub CopyTableData(loSource As Excel.ListObject, loTarget As Excel.ListObject)
Dim lSourceRowCount As Long

With loTarget
    If .DataBodyRange.Rows.Count <> loSource.DataBodyRange.Rows.Count Then
        ' clear of target area should clear target num rows and source num columns
        .Range(.Cells(1,1).Address, .Cells(.DataBodyRange.Rows.Count, lSource.RefersToRange.Columns.Count)).Clear

        ' clear rows if source has less than target
        If .DataBodyRange.Rows.Count > loSource.DataBodyRange.Rows.Count Then
            For i = .DataBodyRange.Rows.Count To loSource.DataBodyRange.Rows.Count + 1 Step -1
                .DataBodyRange.Rows(i).Clear
            Next i
        End If

        ' resize list object
        lSourceRowCount = loSource.HeaderRowRange.Rows.Count + _ 
                          loSource.DataBodyRange.Rows.Count

        .Resize .Range.Cells(1).Resize(lSourceRowCount, .Range.Columns.Count)
    End If
    loSource.DataBodyRange.Copy .DataBodyRange.Cells(1)
End With

With loTarget
    If .DataBodyRange.Rows.Count <> loSource.DataBodyRange.Rows.Count Then
        ' clear of target area should clear target num rows and source num columns
        .Range(.Cells(1,1).Address, .Cells(.DataBodyRange.Rows.Count, lSource.RefersToRange.Columns.Count)).Clear

        ' clear rows if source has less than target
        If .DataBodyRange.Rows.Count > loSource.DataBodyRange.Rows.Count Then
            For i = .DataBodyRange.Rows.Count To loSource.DataBodyRange.Rows.Count + 1 Step -1
                .DataBodyRange.Rows(i).Clear
            Next i
        End If

        ' resize list object
        lSourceRowCount = loSource.HeaderRowRange.Rows.Count + _ 
                          loSource.DataBodyRange.Rows.Count

        .Resize .Range.Cells(1).Resize(lSourceRowCount, .Range.Columns.Count)
    End If
    loSource.DataBodyRange.Copy .DataBodyRange.Cells(1)
End With

End Sub

End Sub

Thanks again.

再次感谢。