VB refresh of query

[ndn14]

Okay here is the code used to open a workbook, refresh the query, save the
work book and exit out of it. Problem is that the save script acts prior to
the refresh of the query completing and get an error "This action will cancel
a pending Refresh Data Command." How do I allow all queries to refresh prior
to saving and closing the workbook?

Sub Refresh()
'
' Refresh Macro
' Macro recorded 5/3/2007 by Authorized User
'
' Keyboard Shortcut: Ctrl+Shift+R
'
ChDir "File Location"
Workbooks.Open Filename:= _
"File name"
ActiveWorkbook.RefreshAll
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Control").Select
ActiveWorkbook.Save
ActiveWindow.Close

End Sub

 

[Vergel Adriano]

One way is to refresh the queries sequentially and not refresh them on the
background. This way, control is returned to the code only after the query
is finished refreshing. I think depending on the size of the queries, you
might have some performance problems with this approach... but anyway, try
something like this:

Sub Test
Dim qry As QueryTable
Dim sht As Worksheet
Dim wb As Workbook

Set wb = Workbooks.Open("C:\sample.xls")
For Each sht In wb.Worksheets
For Each qry In Sheet1.QueryTables
qry.Refresh BackgroundQuery:=False
Next qry
Next sht
wb.Save
wb.Close
End Sub

 

[Don Guillett]

From HELP
Refresh Method
See AlsoApplies ToExampleSpecifics
Refresh method as it applies to the ListObject object.

Retrieves the current data and schema for the list from the server that is
running Microsoft Windows SharePoint Services. This method can be used only
with lists that are linked to a SharePoint site. If the SharePoint site is
not available, calling this method will return an error.

expression.Refresh()

expression Required. An expression that returns a ListObject object.

Remarks
Calling the Refresh method does not commit changes to the list in the Excel
workbook. Uncommitted changes in the list in Excel are discarded when the
Refresh method is called. To avoid losing any uncommitted changes, call the
UpdateChanges method of the ListObject object before calling the Refresh
method.

Refresh method as it applies to the QueryTable object.

Updates an external data range (QueryTable). Boolean.

expression.Refresh(BackgroundQuery)

expression Required. An expression that returns a QueryTable object.

BackgroundQuery Optional Variant. Used only with QueryTables that are
based on the results of a SQL query. True to return control to the procedure
as soon as a database connection is made and the the query is submitted. The
QueryTable is updated in the background. False to return control to the
procedure only after all data has been fetched to the worksheet. If this
argument isn't specified, the setting of the BackgroundQuery property
determines the query mode