刷新工作簿中的所有查询 [英] Refresh All Queries in Workbook

问题描述

这适用于.xls书籍，但是否也可以更改.xlsx工作簿?还是它们的语法对两者都适用?

This works for .xls books, but can it be altered for .xlsx workbooks as well? Or is their syntax that will work for both?

Option Explicit
Public Sub RefreshQueries()
  Dim wks As Worksheet
  Dim qt As QueryTable
  For Each wks In Worksheets
    For Each qt In wks.QueryTables
        qt.Refresh BackgroundQuery:=False
    Next qt
  Next wks
  Set qt = Nothing
  Set wks = Nothing
End Sub

编辑-因此，似乎我的语法确实刷新了.xlsx工作簿，但没有刷新来自sql server的查询.如何通过VBA刷新这些内容.

EDIT -- So it seems my syntax does refresh .xlsx workbooks, but not queries that are from sql server. How can those be refreshed via VBA.

推荐答案

首先，在 .xlsx 工作簿中没有宏将起作用，因为.xlsx工作簿不能包含宏-您需要另存为已启用宏的工作簿，其扩展名为 .xlsm .

First, no macro will work in a .xlsx workbook because .xlsx workbooks can't contain macros - you need to save as a macro-enabled workbook which has the extension .xlsm.

在Excel 2007和更高版本中，用户创建的与SQL Server数据源(以及其他)的外部数据连接将不会产生QueryTables成员，而是会产生一个ListObject，该对象具有一个QueryTable对象，可以通过进行访问ListObject.QueryTable 属性-请参见Dick Kusleika的 answer 到

In Excel 2007 and later, user created external data connections to SQL Server data sources (amongst others) will result not in a QueryTables member, but in a ListObject which will possess a QueryTable object that can be accessed via the ListObject.QueryTable property - see Dick Kusleika's answer to this question. The following code should refresh both types of queries:

Option Explicit
Public Sub RefreshQueries()

  Dim wks As Worksheet
  Dim qt As QueryTable
  Dim lo As ListObject

  For Each wks In Worksheets
    For Each qt In wks.QueryTables
        qt.Refresh BackgroundQuery:=False
    Next qt

    For Each lo In wks.ListObjects
        lo.QueryTable.Refresh BackgroundQuery:=False
    Next lo

  Next wks

  Set qt = Nothing
  Set wks = Nothing
End Sub

我以前不熟悉 ListObject 类型，所以我不知道您是否可以在没有的工作表上使用 ListObject > QueryTable ，这可能会导致上述代码出错-您可能需要检查一下.

I wasn't previously familiar with the ListObject type so I don't know if you can have a ListObject on a worksheet that doesn't have a QueryTable, which might cause an error in the above code - you might need to check for this.

这篇关于刷新工作簿中的所有查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！