Excel VBA 快速访问 Power Query 表格数据

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

 运行结果：

以上