title: Excel VBA and XML - clear table data
body: <p>I've created a spreadsheet with multiple worksheets, each containing XML mapped table elements from a number of different XML files.</p>
  
  <p>I'm using VBA to refresh the data by pointing to a given file name in the current directory. However, if the file doesn't exist I want it to clear any existing data in the XML table.</p>
  
  <p>If I delete the map the data remains and the link is lost.</p>
  
  <p>Something like (and I don't know the correct syntax):</p>
  
  <pre><code>Sub ClearTables()
  Dim mySheet As Worksheet
  Dim myTable As ListObject
  
  For Each mySheet In ThisWorkbook.Worksheets
      For Each myTable In mySheet.ListObjects
          'MsgBox myTable.Name
          myTable.DataBodyRange.Select
          ActiveCell.ClearContents
      Next myTable
  Next mySheet
  End Sub
  </code></pre>
  
  <p>Does anyone have any ideas as to how I could achieve this. I'd rather not have to parameterise and set the names of nearly 50+ tables when I want them all cleared at the begging of the macro.</p>
  
  <p>All I keep getting is an error "Object variable or With block variable not set".</p>
  
  <p>UPDATE: I've managed to fix this by testing for empty tables. The problem was due to some tables being empty already. The code I'm using is as follows:</p>
  
  <pre><code>Sub ClearTables()
  Dim mySheet As Worksheet
  Dim myTable As ListObject
  
  For Each mySheet In ActiveWorkbook.Worksheets
      For Each myTable In mySheet.ListObjects
          'MsgBox myTable.Name
          'myTable.DataBodyRange.ClearContents
          If Not myTable.DataBodyRange Is Nothing Then
              myTable.DataBodyRange.ClearContents
          End If
      Next myTable
  Next mySheet
  End Sub
  </code></pre>