none
Macro run after webquery finished working RRS feed

  • Question

  • Dear All

    How to solve a problem when macro starts running before previous one not finished refreshing web queries?

    My macro look like this:

    Sub RunAll()
        Call RefreshQueryTables
        Call ChangePriceFormat
        Call TextToColumn
        Call Get_Report
    End Sub

    So before "ChangePriceFormat" macro start the "RefreshQueryTables" have to finish working.

    Refreshing lasts about 3-4 minutes.

    "RefreshQueryTables" looks like:

    Sub RefreshQueryTables()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim qt As QueryTable
    Dim WS As Worksheet

    For Each WS In ActiveWorkbook.Sheets
        For Each qt In WS.QueryTables
            Debug.Print qt.Name
            qt.Refresh
        Next
    Next
    End Sub

    I have tried this:

    Sub RunAll()
        Call RefreshQueryTables
        ActiveWorkbook.RefreshAll
    DoEvents
        Call ChangePriceFormat
        Call TextToColumn
        Call Get_Report
    End Sub

    but DoEvents does not affect on background queries

    maybe I need to use

    .Refresh BackgroundQuery:=False

    but how to do this in this case? I am rather basic in VBA




    Tuesday, November 18, 2014 12:08 PM

Answers

  • Hello,

    you're almost there.

    Sub RefreshQueryTables()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim qt As QueryTable
    Dim WS As Worksheet

    For Each WS In ActiveWorkbook.Sheets
        For Each qt In WS.QueryTables
            Debug.Print qt.Name
           call qt.Refresh(false)
        Next
    Next
    End Sub

    or write: qt.Refresh  BackgroundQuery:=False

    Tuesday, November 18, 2014 2:37 PM

All replies

  • Hello,

    you're almost there.

    Sub RefreshQueryTables()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim qt As QueryTable
    Dim WS As Worksheet

    For Each WS In ActiveWorkbook.Sheets
        For Each qt In WS.QueryTables
            Debug.Print qt.Name
           call qt.Refresh(false)
        Next
    Next
    End Sub

    or write: qt.Refresh  BackgroundQuery:=False

    Tuesday, November 18, 2014 2:37 PM
  • Hi Gordonik,
     
    Replace qt.Refresh
     
    with
     
    qt.Refresh BackgroundQuery:=False
     
     
     
     

    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Tuesday, November 18, 2014 3:18 PM
  • Thanks Guys

    Working!

    G

    Friday, November 21, 2014 10:55 AM