Putting Tables To Work In Excel 2010 – Errata

A week or so ago I included some code in a Blog about using Tables in Excel 2010.  In that blog I included some code that, among other stuff, was supposed to clear all the existing rows of data in the table before importing  new. 

Unfortunately the code I included was wrong in that it would have only deleted 1 row of data beforehand and not all the data in the Table.  You have to iterate through all the rows to delete everything without deleting the Table itself.  If you delete the table and recreate it any formulas referring to the Table name will go to !Refs# and this is not a good thing.

Here is the code with the old code commented out and the new code bold:

 Sub GetWeeklyData()

   Dim Swbk As Workbook
  
   With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
   
    Set Swbk = Application.Workbooks.Open(Range(“EDIDir”).Value & Range(“RequirementsWeeklyFile”).Value, , True)
   
    ThisWorkbook.Activate
  
    With Worksheets(“EDIReleasesWeekly”)

      For i = 1 to .ListObjects(“EDIReleasesWeekly”).ListRows.count  
          .ListObjects(“EDIReleasesWeekly”).ListRows(1).Delete
     Next i

 ‘ .ListObjects(“EDIReleasesWeekly”).ListRows(1).Delete

        Swbk.Worksheets(1).UsedRange.Cells.Copy
        .Cells(1, 1).PasteSpecial Paste:=xlValues
        .Cells(1, 1).PasteSpecial Paste:=xlFormats
        .ListObjects(“EDIReleasesWeekly”).Resize Range(.Cells1,1).CurrentRegion.Address)
    End With
   
    Application.CutCopyMode = False
   
    Swbk.Close False
   
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
   
End Sub