Excel VBA 使用选定的工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14367482/
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 Use selected sheet
提问by user1985112
Excel VBA newbie here. I just need a macro that will refresh the queries I have on a single sheet I'm viewing. I already have the refresh macro but I always have to specify the sheet name that I want to refresh. Is it possible to have the macro run on whatever sheet I'm viewing? Here's the macro in it's current state:
Excel VBA 新手在这里。我只需要一个宏来刷新我正在查看的单个工作表上的查询。我已经有了刷新宏，但我总是必须指定要刷新的工作表名称。是否可以在我正在查看的任何工作表上运行宏？这是当前状态下的宏：
Sub Refresh_Query()
Sheets("Sheet1").Select
Range("B6").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub
回答by LittleBobbyTables - Au Revtheitroad
You want to use ActiveSheet.Name, such as:
您要使用ActiveSheet.Name，例如：
Sub Refresh_Query()
Sheets(ActiveSheet.Name).Select
Range("B6").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub
回答by chuff
This should work:
这应该有效：
Sub Refresh_Query()
ActiveSheet.QueryTables(1).Refresh BackgroundQuery:=False
End Sub
回答by chris neilsen
The OP is ambiguous: text asks to refresh all query tables on the active sheet, but sample code only refreshes the one query table that contains cell B3
OP 含糊不清：文本要求刷新活动工作表上的所有查询表，但示例代码仅刷新包含单元格的一个查询表 B3
To refresh only one query table use
只刷新一个查询表使用
Sub RefreshOneQuery()
Dim qt As QueryTable
On Error Resume Next ' in case there is no qt containing cell B6
Set qt = Range("B6").QueryTable
On Error GoTo 0
If Not qt Is Nothing Then
qt.Refresh BackgroundQuery:=False
End If
End Sub
To refresh all query tables on the sheet use
要刷新工作表上的所有查询表，请使用
Sub RefreshAllQueries()
Dim qt As QueryTable
For Each qt In ActiveSheet.QueryTables
qt.Refresh BackgroundQuery:=False
Next
End Sub
