Errata2

I have always worried about giving code advice here for the simple reason that I might be misleading people with wrong, or inefficeient code or code that I thought worked in all conditions but subsequently could be proven otherwise.

Well here it is in double Errata-mode ads I have found that the code I added in my first Errata on this topic is itself in error because it is not efficient and in many cases it will fail in a way that is not acceptable.

In the code in the last post I added a For loop that would cycle through each row in the Table’s Records one at a time deleting the first record over and over until done.  I thought that was an inefficient way to accomplish my goal of deleting  all the records in the Table but it seemed to work.  Then I looked at it again this afternoon and played around until I found a way to delete all records in the Table in one pass.  I do not find this technique completely intuitive but it seems to work for me:

First I set “LO” as a ListObject-type Variable and assign our Table to it:

    Set LO = Worksheets(“EDIReleasesWeekly”).ListObjects(“EDIReleasesWeekly”)

Then instead of :

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

I use:

    If Not LO.DataBodyRange Is Nothing Then
        LO.DataBodyRange.Rows.Delete
    End If

Note the If statement it is wrapped in … If there are no records in the Table to begin with (for whatever reason) it will not fail when trying to Delete records from a DataBodyRange that doesn’t exist and would throw an error if used.

I have also changed:

   .ListObjects(“EDIReleasesWeekly”).Resize  Range (.Cells1,1).CurrentRegion.Address)

to:

     LO.Resize Range(LO.DataBodyRange.Cells(1, 1).CurrentRegion.Address)

Here is yet another cleaned up code sample – Sorry for the confusion:

Sub GetWeeklyData()

    Dim Swbk As Workbook
    Dim LO As ListObject

    Set LO = Worksheets(“EDIReleasesWeekly”).ListObjects(“EDIReleasesWeekly”)
   
    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
 
    If Not LO.DataBodyRange Is Nothing Then
        LO.DataBodyRange.Rows.Delete
    End If

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