none
Background refresh turned off but queries still run in the background! RRS feed

  • Question

  • Very frustrating problem. I have several power queries in an excel file which I am trying to refresh one at a time.When the file is first opened I run code to:

    With oledbconnections:

    If .Refreshing Then .CancelRefresh

    .RefreshOnFileOpen = False

    .BackgroundQuery = False

    .EnableRefresh = False

    End With

    For querytables:

    qt.BackgroundQuery = False

    When it is time to update I I loop through the qeuries like this:

    With oledbconnections:

     .BackgroundQuery = False
      .RefreshPeriod = 0
      If EnableRefresh = False Then
      .EnableRefresh = True
      End If
      .Refresh
      .MaintainConnection = False
      If EnableRefresh = True Then
      .EnableRefresh = False
      End If
     .RefreshOnFileOpen = False

    end

    Between each query and at the end of the macro I run Application.CalculateUntilAsyncQueriesDone and DoEvents.

    This seems to work for a while and then I may notice things running slowly or I may get a "frown" error message in the middle of the process but the remainder of any quries seem to process fine.

    Click on Data_Show Queries and lo and behold they will all have their little refreshing circle active. If I open to edit I will get a message that they cannot be edited while refreshing. After canceling them all I can edit them or view the properties in the Data Connections_Properties and everything looks as it should: (sorry I can;t post a image of the connection properties showing everything disabled).


    I can't believe I cannot do something as simple as pull in data from some Excel files and Oracle tables on a consistent basis. I am close to the point of ditching power query and just running SQL queries directly from a macro. I see lot's of posts about this with most being resolved by setting background query to false or messing with various refresh settings. BTW, I have messed around adding and subtracting the connection properties and had the best luck with the parameters in the code above.

    Unfortunately, best of the worst is only helpful in an ugly contest and I need a reliable data solution.

    Any thoughts or suggestions?

    JMK

    Saturday, September 7, 2019 6:33 PM

All replies

  • Hi,

    To refresh your queries in a sequential order, the idea to write a macro that sets the query's background refresh to false, then refreshes the query, then sets the background refresh back to true. As you mentioned, there are a lot of threads online about this particular task and I'd like to suggest you follow the proposed solution.

    Regarding the error that you are experiencing, please submit a frown next time you hit it. Also once the error happens, please go to File > Account > About Excel > copy the Session ID value and paste it in your reply here.

    Hope it helps.


    Guy Hunkin - Excel Team

    Monday, September 16, 2019 2:37 PM
  • Thanks for your reply.

    Is there any reason to turn the background refresh back to True as opposed to leaving it set as false? I'd prefer not to risk any queries being triggered and updating  while the file is open. The goal is to have have an auditable data trail not one that can change after I've made a decision based on information gleaned from queries.

    My latest attempt  is as inconsistent as the others - sometimes it works sometimes it doesnt. First I cycle through all queries to see if they are running and stop them. 

    For Each cnn In ActiveWorkbook.Connections
            With cnn.OLEDBConnection
                    If .Refreshing Then
                        .CancelRefresh
                    End If
                .RefreshOnFileOpen = False
                .BackgroundQuery = False
                    If .EnableRefresh = True Then
                        .EnableRefresh = False
                    End If
           End With
    Next

    Then I updated each query with these parameters. After each I

     With con.OLEDBConnection
                .BackgroundQuery = False
                .RefreshPeriod = 0
                myertest = .EnableRefresh
                On Error Resume Next
                If .EnableRefresh = False Then
                    .EnableRefresh = True
                End If
                On Error GoTo 0
                .Refresh
                .MaintainConnection = False
                If EnableRefresh = True Then
                    .EnableRefresh = False
                End If
                .RefreshOnFileOpen = False
            End With

    before starting and after each query runs I added

    Application.CalculateUntilAsyncQueriesDone
    DoEvents

    After doing all of that, I may or may not get 1 to 3 frown messages (clicking submit does absolutely nothing) and there is a 50/50 chance that if I click the show queries menu when the macro is complete I will one or more queries spinning away in refresh mode for eternity like this.


    I do not see any session ID in the about window. Just MS version, product ID and a scroll box with various copyright information.

    This is mind-boggling.

    Monday, October 7, 2019 8:57 PM
  • Taking a step back here. When you click Data _ Show Queries - do spinning circles mean a query is refreshing? I would think so as there are occasions when they are not spinning and if I click edit while spinning the editor opens with a message that the query cannot be edited while refreshing. To check this, while the query list showed all the queries refreshing - I ran a macro that listed each workbook connection and printed its ".refreshing" status. I did the same looping through all the listobjects in each worksheet and if they were a query table - printing the .refreshing status. During the macro the query list showed this:

    The results of the "test" for these queries is:

    "Query - Import Daily FX Rates" connection refreshing status is False

    "Import_Daily_FX_Rates" querytable refresh status is False

    "Query - Import Budget FX Rates" connection refreshing status is False

    "Import_Budget_FX_Rates" querytable refresh status is False

    Am I missing something really, really basic here or is it time to throw in the towel with Power Query since  I cannot gain comfort over whether a query is up to date and complete.

    BTW, the spinning query image above was taken after I ran a macro to change then .enablerefresh setting of all querytables and connections to "False". I'd expect that would eliminate the possibility of the queries to refresh at all.

    Tuesday, October 8, 2019 7:14 PM
  • So still searching for the holy grail. Latest attempt involves setting a class to trigger an after refresh event. This is much more reliable and occasionally it will actually loop through all 8 queries. With queries displayed, you can watch them refresh and stop refreshing one at a time which is the desired result. I saved my file and prepared to celebrate.

    Than I ran the update macros the next day. The frst macro triggered what I will nw refer to as a Microsoft clown error (the frown error). After clicking OK the rest processed without any clown errors but lo and behold they all showed as refreshing in the show queries window.

    I manually stopped all queries as they do not respond to vba .cancelrefresh when they are in this state, ran the query again and it worked. I can keep the file open and run the query throughout the day and sometimes it works correctly - sometimes I get one or more clown queries and all the queries show as refreshing for ever.

    Any ideas on how to make this work reliably? I will open a new thread on deciphering the clown error messages.

    Monday, October 14, 2019 1:08 PM
  • After another 4 hour tour I am giving up. Tomorrow will be reversion day - going back to SQL for Oracle data, ADO for Excel data and arrays to import csv files.

    I did some last ditch testing before giving up. First I tried to import just a few few Excel and csv queries using a class module to trigger a query table after refresh event. It would work fine 3 or 4 times and then I'd get a clown/frown error and all the queries in the query pane would show as refreshing forever until you manually stop them. If you check the query.refreshing value using vba or vba editor the queries showed a refreshing value of false. So either query pane or property value is wrong. Either way I can't trust that the data I import is accurate or complete so it's useless.

    Then I started over without VBA. Set up a few queries (excel, csv and oracle) using Excel GUI. Made sure connection properties were set to allow all to refresh when refresh all was triggered and also made sure that all had background refresh set to false. Worked a few times and then the clown/frown error would pitch up and all queries in the queries pane would display as refreshing forever. In vba queries showed refreshing = false. That eliminated the possibility that the power query issues were caused by attempts to automate using VBA.

    Most users probably don't bother to open the queries pane to check if a query is really complete or if there are any queries that have errors or warnings. Makes me wonder how many bazillion Excel users are using potentially inaccurate data in their worksheets and what kind of decisions are being made using the suspect data.

    FYI I'm using Office Pro Plus 2016 in a corporate environment that keeps its software up to date.

    Monday, October 14, 2019 6:39 PM

  • Then I started over without VBA. Set up a few queries (excel, csv and oracle) using Excel GUI. Made sure connection properties were set to allow all to refresh when refresh all was triggered and also made sure that all had background refresh set to false. Worked a few times and then the clown/frown error would pitch up and all queries in the queries pane would display as refreshing forever. In vba queries showed refreshing = false. That eliminated the possibility that the power query issues were caused by attempts to automate using VBA.

    Most users probably don't bother to open the queries pane to check if a query is really complete or if there are any queries that have errors or warnings. Makes me wonder how many bazillion Excel users are using potentially inaccurate data in their worksheets and what kind of decisions are being made using the suspect data.

    FYI I'm using Office Pro Plus 2016 in a corporate environment that keeps its software up to date.

    Sorry to hear that you're having these sort of issues. These sound alarming.

    If you happen to reproduce the frown error, then perhaps it could be because of how your queries are setup and what they're trying to do.

    You're right in the sense that most users don't really use the Queries pane to check the if the queries are done refreshing or not, as they tend to use the "status bar" at the bottom of the Excel window to check if the refresh is happening or if it's finished. What happens if you turn that Refresh in background to true? I've never actally set it to false.

    I just leave it as default in most scenarios:

    

    I wonder what results you get if you just leave it as default. 

    I wonder what your queries are doing and if the frown error is exactly the same.

    Tuesday, October 15, 2019 12:17 AM
  • Hi JMKCC,

    Can you please submit a frown next time you encounter an error? Go to File > Feedback > and click the I Don't Like Something button.

    Once you do it, please share your Session ID with me. Go to File > Account > About Excel > copy the Session ID value at the top of the screen and paste in your your reply to me.

    Last but not least, if you have a simple workbook that demonstrates this problem can you please share it with me? Just make sure it doesn't contain any private or sensitive data. 

    It will help us to investigate.


    Guy Hunkin - Excel Team

    Wednesday, October 16, 2019 2:26 PM