Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB refresh of query
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB refresh of query
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 -- Hope that helps. Vergel Adriano "ndn14" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB refresh of query
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 -- Don Guillett SalesAid Software "ndn14" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I 'Enable Automatic Refresh' for Query Refresh by default | Setting up and Configuration of Excel | |||
Query Refresh-Enable Automatic Refresh Dialogue Box | Excel Discussion (Misc queries) | |||
Timing of automatic query refresh and macro pivot table refresh | Excel Programming | |||
Excel does not close from VB!! (when i refresh Refresh query with BackgroundQuery:=False) | Excel Programming | |||
Web Query fail to Refresh All but individual refresh is ok | Excel Programming |