Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Refresh Data mulitple queries (Excel 2003 SP2)

    A worksheet has data populated by 6 different database queries. I need to put one button on the worksheet that refreshes the data from all 6 queries. However the code below generates an error that says "This operation can not be done because the data is refreshing in the background." Basically, it is looping faster than the database can refresh the data. Is there a method or event in the QueryTables that tells me when one query is done? What other way can I test to be sure each query is done before issuing the Refresh command for the next one?

    Thanks folks.

    Private Sub cmdRefresh_Click()
    Dim i As Integer
    Dim wb As Workbook
    Dim ws As Worksheet


    ActiveWorkbook.Worksheets("Active").Select

    MsgBox "Updateing the Active Accounts worksheet. Please wait.", vbOKOnly, "ACTIVE SHEET UPDATE"

    Set wb = ActiveWorkbook
    Set ws = wb.Worksheets("Active_Accts")

    For i = 1 To ws.QueryTables.Count
    ws.QueryTables(i).Refresh
    Next i

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refresh Data mulitple queries (Excel 2003 SP2)

    Try
    <code>
    ws.QueryTables(i).Refresh(BackgroundQuery:=False)
    </code>
    This specifies that the code should wait until the refreshing of the query table has finished, instead of continuing while the refreshing takes place in the background.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Refresh Data mulitple queries (Excel 2003 SP2)

    I did not test this but , you could try the "Refreshing" propery of the query table:

    For i = 1 To ws.QueryTables.Count
    with ws.QueryTables(i)
    .Refresh
    Do While .refreshing
    loop
    end with
    Next i

    This should keep it looping until the refreshing is done...

    Steve

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh Data mulitple queries (Excel 2003 SP2)

    If there are no other queries in the workbook and no pivot tables you do not want refreshed, this works as well:

    ActiveWorkbook.RefreshAll
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •