Excel VBA 和 XML - 清除表格数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23953221/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA and XML - clear table data
提问by user3691037
I've created a spreadsheet with multiple worksheets, each containing XML mapped table elements from a number of different XML files.
我创建了一个包含多个工作表的电子表格，每个工作表都包含来自许多不同 XML 文件的 XML 映射表元素。
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.
我正在使用 VBA 通过指向当前目录中的给定文件名来刷新数据。但是，如果该文件不存在，我希望它清除 XML 表中的任何现有数据。
If I delete the map the data remains and the link is lost.
如果我删除地图，数据仍然存在并且链接丢失。
Something like (and I don't know the correct syntax):
类似的东西（我不知道正确的语法）：
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
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.
有没有人对我如何实现这一目标有任何想法。当我希望在宏请求时将它们全部清除时，我宁愿不必参数化和设置近 50 个表的名称。
All I keep getting is an error "Object variable or With block variable not set".
我一直得到的是一个错误“对象变量或块变量未设置”。
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:
更新：我通过测试空表设法解决了这个问题。问题是由于一些表已经空了。我使用的代码如下：
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
回答by
How about:
怎么样：
For Each mySheet In ThisWorkbook.Sheets
For Each myTable In mySheet.ListObjects
myTable.DataBodyRange.ClearContents
Next myTable
Next mySheet
Please note that the code does not test if the object is a table... actually I'm not sure what other ListObject- class objects you might have in the worksheets.
请注意，代码不会测试对象是否是表...实际上我不确定ListObject您在工作表中可能有哪些其他类对象。
