Closed Thread
Results 1 to 7 of 7

Help! Query Refreshing

  1. #1
    Emma Hope
    Guest

    Help! Query Refreshing

    Hi All,

    I have a excel workbook with a number of tabs, each with an MSQuery from
    Access. Each one takes seconds to refresh. I need to refresh most of these
    once and then cycle through a list of names, refreshing two more for each
    name (these have a parameter looking at an cell to obtain the name), the
    workbook is then saved and the process loops round again.

    When running the loop once, it works no problem, the data refreshes and has
    finished refreshing but once i use the loop properly, the code stops at
    'Worksheets("TTP").QueryTables("TTPQuery").Refresh' and tells me it cannot do
    this because it is refreshing in the background.

    Please can someone tell me either what is wrong with my code OR how to pause
    the code until the refresh finishes (but i don't think it is actually the
    problem).

    Thanks
    Emma

    Sub CycleThroughPlanners()
    Dim iCount As Long
    Dim strPlanner As String
    Dim strPath As String

    Worksheets("Data").QueryTables("DataPlannerQuery1").Refresh
    Worksheets("Data").QueryTables("DataPlannerQuery2").Refresh
    Worksheets("Figures").QueryTables("FiguresPlannerQuery").Refresh
    Worksheets("AMFPData").QueryTables("AMFPQuery1").Refresh
    Worksheets("AMFPData").QueryTables("AMFPQuery2").Refresh
    Worksheets("AMFPData").QueryTables("AMFPQuery3").Refresh
    iCount = 2

    Do While Worksheets("Data").Cells(iCount, 12) <> ""
    strPlanner = Worksheets("Data").Cells(iCount, 12)
    Worksheets("Data").Range("C14").Value = strPlanner

    'Workbooks(1).RefreshAll

    Worksheets("TTP").QueryTables("TTPQuery").Refresh
    Worksheets("Pending").QueryTables("PendingQuery").Refresh

    strPath = Worksheets("Data").Range("B16")
    Sheets("One To One").Select
    Range("A2:AN2").Select
    Sheets("One To One").Select
    Sheets("One To One").Copy
    ActiveWorkbook.SaveAs Filename:=strPath, FileFormat:=xlNormal, Password:="",
    WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWorkbook.Close
    iCount = iCount + 1
    Loop

    End Sub

  2. #2
    K Dales
    Guest

    RE: Help! Query Refreshing

    Despite your skepticism it is possible that the 1st query is not yet done to
    give the parameters for the second one (it may work fine for the first
    iteration due to some overhead time setting up the connection and reading the
    table structure and query definition, the Access driver may save some of this
    info to help speed the queries the 2nd time around and thus the problem
    occurs only if you try to repeat the queries).

    Two things to try:
    1) To make sure one query finishes before the next begins, either manually
    set the querytables to not allow background processing (right click on the
    querytable and set the properties) or in code set the .BackgroundQuery
    property to false.
    2) To wait until a query is done, use a loop to check its .Refreshing
    property. But if you do this it is advisable also to have a time check
    within the loop to avoid potential problems if the database doesn't respond;
    here is an example:

    CheckTime = Now() + TimeValue("00:01:00")
    While MyQueryTable.Refreshing and CheckTime > Now()
    DoEvents
    ' Add any other processing you want done during the wait time; e.g.
    updating a status bar message, etc.
    WEnd

    --
    - K Dales


    "Emma Hope" wrote:

    > Hi All,
    >
    > I have a excel workbook with a number of tabs, each with an MSQuery from
    > Access. Each one takes seconds to refresh. I need to refresh most of these
    > once and then cycle through a list of names, refreshing two more for each
    > name (these have a parameter looking at an cell to obtain the name), the
    > workbook is then saved and the process loops round again.
    >
    > When running the loop once, it works no problem, the data refreshes and has
    > finished refreshing but once i use the loop properly, the code stops at
    > 'Worksheets("TTP").QueryTables("TTPQuery").Refresh' and tells me it cannot do
    > this because it is refreshing in the background.
    >
    > Please can someone tell me either what is wrong with my code OR how to pause
    > the code until the refresh finishes (but i don't think it is actually the
    > problem).
    >
    > Thanks
    > Emma
    >
    > Sub CycleThroughPlanners()
    > Dim iCount As Long
    > Dim strPlanner As String
    > Dim strPath As String
    >
    > Worksheets("Data").QueryTables("DataPlannerQuery1").Refresh
    > Worksheets("Data").QueryTables("DataPlannerQuery2").Refresh
    > Worksheets("Figures").QueryTables("FiguresPlannerQuery").Refresh
    > Worksheets("AMFPData").QueryTables("AMFPQuery1").Refresh
    > Worksheets("AMFPData").QueryTables("AMFPQuery2").Refresh
    > Worksheets("AMFPData").QueryTables("AMFPQuery3").Refresh
    > iCount = 2
    >
    > Do While Worksheets("Data").Cells(iCount, 12) <> ""
    > strPlanner = Worksheets("Data").Cells(iCount, 12)
    > Worksheets("Data").Range("C14").Value = strPlanner
    >
    > 'Workbooks(1).RefreshAll
    >
    > Worksheets("TTP").QueryTables("TTPQuery").Refresh
    > Worksheets("Pending").QueryTables("PendingQuery").Refresh
    >
    > strPath = Worksheets("Data").Range("B16")
    > Sheets("One To One").Select
    > Range("A2:AN2").Select
    > Sheets("One To One").Select
    > Sheets("One To One").Copy
    > ActiveWorkbook.SaveAs Filename:=strPath, FileFormat:=xlNormal, Password:="",
    > WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    > ActiveWorkbook.Close
    > iCount = iCount + 1
    > Loop
    >
    > End Sub


  3. #3
    Emma Hope
    Guest

    RE: Help! Query Refreshing

    Hi K.

    I tried Step 1. the background refresh = false thing and it worked
    perfectly! Thanks so much for your help.

    Thanks
    Emma

    "K Dales" wrote:

    > Despite your skepticism it is possible that the 1st query is not yet done to
    > give the parameters for the second one (it may work fine for the first
    > iteration due to some overhead time setting up the connection and reading the
    > table structure and query definition, the Access driver may save some of this
    > info to help speed the queries the 2nd time around and thus the problem
    > occurs only if you try to repeat the queries).
    >
    > Two things to try:
    > 1) To make sure one query finishes before the next begins, either manually
    > set the querytables to not allow background processing (right click on the
    > querytable and set the properties) or in code set the .BackgroundQuery
    > property to false.
    > 2) To wait until a query is done, use a loop to check its .Refreshing
    > property. But if you do this it is advisable also to have a time check
    > within the loop to avoid potential problems if the database doesn't respond;
    > here is an example:
    >
    > CheckTime = Now() + TimeValue("00:01:00")
    > While MyQueryTable.Refreshing and CheckTime > Now()
    > DoEvents
    > ' Add any other processing you want done during the wait time; e.g.
    > updating a status bar message, etc.
    > WEnd
    >
    > --
    > - K Dales
    >
    >
    > "Emma Hope" wrote:
    >
    > > Hi All,
    > >
    > > I have a excel workbook with a number of tabs, each with an MSQuery from
    > > Access. Each one takes seconds to refresh. I need to refresh most of these
    > > once and then cycle through a list of names, refreshing two more for each
    > > name (these have a parameter looking at an cell to obtain the name), the
    > > workbook is then saved and the process loops round again.
    > >
    > > When running the loop once, it works no problem, the data refreshes and has
    > > finished refreshing but once i use the loop properly, the code stops at
    > > 'Worksheets("TTP").QueryTables("TTPQuery").Refresh' and tells me it cannot do
    > > this because it is refreshing in the background.
    > >
    > > Please can someone tell me either what is wrong with my code OR how to pause
    > > the code until the refresh finishes (but i don't think it is actually the
    > > problem).
    > >
    > > Thanks
    > > Emma
    > >
    > > Sub CycleThroughPlanners()
    > > Dim iCount As Long
    > > Dim strPlanner As String
    > > Dim strPath As String
    > >
    > > Worksheets("Data").QueryTables("DataPlannerQuery1").Refresh
    > > Worksheets("Data").QueryTables("DataPlannerQuery2").Refresh
    > > Worksheets("Figures").QueryTables("FiguresPlannerQuery").Refresh
    > > Worksheets("AMFPData").QueryTables("AMFPQuery1").Refresh
    > > Worksheets("AMFPData").QueryTables("AMFPQuery2").Refresh
    > > Worksheets("AMFPData").QueryTables("AMFPQuery3").Refresh
    > > iCount = 2
    > >
    > > Do While Worksheets("Data").Cells(iCount, 12) <> ""
    > > strPlanner = Worksheets("Data").Cells(iCount, 12)
    > > Worksheets("Data").Range("C14").Value = strPlanner
    > >
    > > 'Workbooks(1).RefreshAll
    > >
    > > Worksheets("TTP").QueryTables("TTPQuery").Refresh
    > > Worksheets("Pending").QueryTables("PendingQuery").Refresh
    > >
    > > strPath = Worksheets("Data").Range("B16")
    > > Sheets("One To One").Select
    > > Range("A2:AN2").Select
    > > Sheets("One To One").Select
    > > Sheets("One To One").Copy
    > > ActiveWorkbook.SaveAs Filename:=strPath, FileFormat:=xlNormal, Password:="",
    > > WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    > > ActiveWorkbook.Close
    > > iCount = iCount + 1
    > > Loop
    > >
    > > End Sub


  4. #4
    Registered User
    Join Date
    10-17-2012
    Location
    Palo Alto, United States
    MS-Off Ver
    Excel 365
    Posts
    6

    Re: Help! Query Refreshing

    Thank you everyone!
    Very hard to find this information.
    Somehow the syntax has changed somewhat, but still not 100%. The loop runs but the state of .REFRESHING doesn't change until I breakpoint or control-break, then it becomes FALSE.

    Please Login or Register  to view this content.
    Last edited by HansSchulze; 05-16-2020 at 02:18 PM.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,067

    Re: Help! Query Refreshing

    Quote Originally Posted by HansSchulze View Post
    Thank you everyone!
    Very hard to find this information.
    Somehow the syntax has changed somewhat, but still not 100%. The loop runs but the state of .REFRESHING doesn't change until I breakpoint or control-break, then it becomes FALSE.

    Please Login or Register  to view this content.
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    10-17-2012
    Location
    Palo Alto, United States
    MS-Off Ver
    Excel 365
    Posts
    6

    Re: Help! Query Refreshing

    The main point is that this:
    Please Login or Register  to view this content.
    no longer works. My implementation includes suggested improvements from many sources (Sleep()) to reduce CPU workload. This, when working (will add finalized solution in edit), will become the answer of the day.
    It's not a question, unless someone can solve the last nit. I am still working on it.
    I can post in a new thread, but the OP is quite useful as a background.

  7. #7
    Registered User
    Join Date
    10-17-2012
    Location
    Palo Alto, United States
    MS-Off Ver
    Excel 365
    Posts
    6

    Re: Help! Query Refreshing

    I opened a new thread on the new issue..
    excel-programming-vba-macros/1316362-workbookconnection-refreshing-not-triggering-in-a-while-sleep-1000-loop.html

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1