How can I update Excel table values & headings with an array?

[JTannas]

Hello Everyone,

Can anyone suggest a clean way of updating an excel table from an array that includes new column headings?

I've got a macro that outputs an array of column headings and values to a spreadsheet, and then formats the output as a table.
When updating the information using the macro, I'd like to preserve the same table (so that my spreadsheet formulas stay intact).

The problem is that when I dump the array values into the table range, the table gets deleted.
Any solutions I've found are pretty ugly.

The code goes like this:

[COLOR=#333333]
[/COLOR]'Grab the table if it already exists
For each objTable in MySheet.ListObjects
      If objTable.Name = "MyTable" then
               Set MyTable = MyRange
               Exit For
      End if
Next

'If the table does not exist, create it
If MyTable is Nothing then
       Set MyTable = MySheet.ListObjects.Add(xlSrcRange, MyRange, , xlYes)
       MyTable.Name = "MyTable"
End If

' Perform the data dump
[COLOR=#333333]MyRange.Value2 = Application.Transpose(arrValues)
[/COLOR]

 

[oldbrewer]

if the table already exists do not bring across table headings, just data pasted in the first empty row, then format cells as needed

 

[JTannas]

I would prefer to update the table columns as well, in case any columns are added to the output array.

 

[oldbrewer]

say you have 3 columns and 10 rows of data, and you wish to import 5 columns (ie 2 new columns) with 20 rows of data - this is tricky. Do you have any idea of the likely columns you may add - if so set them up on your table right from the start.

I think you need a spare cell in your main sheet that tells you the number of the first empty row, and to run a macro that looks at existing headings to determine if new headings need to be created.

It is unusual, to say the least, to import data that does not readily fit into your existing table - are you certain it has to be this way?