excel vba操作工作表（ExcelVBA快速访问）
excel vba操作工作表（ExcelVBA快速访问）以上Option Explicit Function PowerQueryTableData(col As Long) As Variant Dim TargetTable As ListObject Dim R As ListRow Set TargetTable = ActiveSheet.ListObjects("腾讯国内新冠疫情风险地区") For Each R In TargetTable.ListRows Debug.Print R.Range.Cells(1 col) Next End Function运行结果：4、遍历列Option Explicit Sub PowerQueryTableData() Dim TargetTable As ListObject Dim C
1、PowerQuery 可以方便获取外部数据，并进行数据清洗，最后将结果返回到工作表中。每次刷新数据后，工作表中返回的数据的行数不是固定的。
2、在VBA中如何方便地访问PowerQuery返回的表格数据呢？可以参照下面的例子。
Option Explicit
Function PowerQueryTableData(row As Long col As Long) As Variant
Dim TargetTable As ListObject
Dim R As ListRow
Set TargetTable = ActiveSheet.ListObjects("腾讯国内新冠疫情风险地区")
PowerQueryTableData = TargetTable.ListRows.Item(row).Range.Cells(1 col)
End Function
运行结果如图：
3、遍历行语句：
Option Explicit
Function PowerQueryTableData(col As Long) As Variant
Dim TargetTable As ListObject
Dim R As ListRow
Set TargetTable = ActiveSheet.ListObjects("腾讯国内新冠疫情风险地区")
For Each R In TargetTable.ListRows
Debug.Print R.Range.Cells(1 col)
Next
End Function
运行结果：
4、遍历列
Option Explicit
Sub PowerQueryTableData()
Dim TargetTable As ListObject
Dim C As ListColumn
Set TargetTable = ActiveSheet.ListObjects("腾讯国内新冠疫情风险地区")
For Each C In TargetTable.ListColumns
Debug.Print C.Range.Address & "=" & C.Name
Next
End Sub
运行结果：
以上
