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

Question

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 ...

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:

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.

Thanks in advance.

You need to try something, i.e., start writing some code, so we have something to help you with. I think the best approach is to delete all but the first row of the table, use the Listobjects Resize method to get the right number of rows and copy in the new columns. I have a somewhat related post on my blog. — Doug Glancy
And here's a good tutorial by Jan Karel Pieterse. One more thing - you'll especially want to look at the ListColumn object. — Doug Glancy
@DougGlancy Thanks for your direction and help. I was able to incorporate code from your blog and the tutorial to get the result I needed. Here is the code: — user2286596
@DougGlancy I would upvote your comments as useful, but I don't have the necessary reputation to do so. — user2286596

user2286596 user2286596 · Accepted Answer · 2013-04-29T17:22:16

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.

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

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

Thanks again.

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

1 Answers