locked
In Excel 2016 Power Queries don't refresh in the background anymore RRS feed

  • Question

  • Hello,

    in Excel 2013 I was using a simple routine to refresh several Power Queries in the background. After migration to Excel 2016, the same routine doesn't work anymore. Everythings seems to go, but actually, when I open the workbook I find that the refreshing process has aborted. It seems that the refreshing routine goes on, but the system doesn't wait for the refreshing process to finish.

    Best, Franco

    Friday, October 9, 2015 6:39 AM

Answers

  • Thanks Franco, you are so right.  Application.Wait is blocking the polling that updates the status.  The Pause method is a reasonable solution, but here is a simpler alternative.

    Sub RefreshEmAndClose()     For Each cn In Application.Workbooks("MacroBook.xlsm").Connections      lTest = InStr(1, cn.OLEDBConnection.Connection, "provider=Microsoft.Mashup.OleDb.1")      If Err.Number <> 0 Then          Err.Clear          Exit For      End If      If lTest > 0 Then cn.Refresh      Next cn      Application.OnTime DateAdd("s", 10, Now), "CloseIt"    End Sub

    Sub CloseIt()      Application.Workbooks("MacroBook.xlsm").Close (True) End Sub


    Tuesday, October 13, 2015 5:33 AM
  • Do you close down excel right after the refresh? We usually see this when users close the workbook directly after refreshing because our status update is polling based, and it doesn’t get a chance to find out that the refresh is done before excel exists. If this is the issue, the workaround is to force all the connections to NON background refresh, and then adding a DoEvents and then Application.Wait of 5-10 seconds to let everything update. If this is not the case, please share more information on the abort experience you mentioned above. Thank you, Gil
    Saturday, October 10, 2015 9:59 AM
  • Hi Franco, Can you use this code instead of your pause method?
    Application.Wait Now + TimeValue("0:00:05")
    Call for it at the end of all the refresh calls, and after a single DoEvents.
    Monday, October 12, 2015 5:24 PM

All replies

  • Could you share the routine and a screenshot of the relevant connection properites dialog?
    Friday, October 9, 2015 8:52 AM
  • Sorry, I'm new in this forum. Trying to insert the screenshot I receive an alert: "Body text cannot contain images or links until we are able to verify your account." What I means? What I have to do?

    Thanks, Franco

    Friday, October 9, 2015 9:37 AM
  • Sorry for the typo. "What it means?"
    Friday, October 9, 2015 9:38 AM
  • Welcome to this forum :) I think you should verify your account. Read more here.
    Friday, October 9, 2015 10:18 AM
  • Anyway, about the connections property, I have "enable background refresh" and "refresh this connection on Refresh All" abled, "Refresh data when Opening the File" disabled. That's all.

    Here it is the routine working in Excel 2013:

    Public Sub UpdatePowerQueriesOnly()
    Dim lTest As Long, cn As WorkbookConnection
    On Error Resume Next
    For Each cn In ThisWorkbook.Connections
    lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1")
    If Err.Number <> 0 Then
    Err.Clear
    Exit For
    End If
    If lTest > 0 Then cn.Refresh
    Next cn
    End Sub

    Furthermore, please note that "Refresh All" Button works fine, but Activeworkbook.RefreshAll in VBA has the same behavior as the routine.


    Friday, October 9, 2015 10:20 AM
  • Can you try to uncheck the box "Enable background refresh" on all the connections, and try again?
    Friday, October 9, 2015 11:44 AM
  • Ok, I tried. The only difference is that the workbook become visible but the refreshing process aborts in the same way.
    Friday, October 9, 2015 12:17 PM
  • Do you close down excel right after the refresh? We usually see this when users close the workbook directly after refreshing because our status update is polling based, and it doesn’t get a chance to find out that the refresh is done before excel exists. If this is the issue, the workaround is to force all the connections to NON background refresh, and then adding a DoEvents and then Application.Wait of 5-10 seconds to let everything update. If this is not the case, please share more information on the abort experience you mentioned above. Thank you, Gil
    Saturday, October 10, 2015 9:59 AM
  • Well Gil, you are right, the workaround works. But I think the Application.Wait solution to be critical, because  the time for update to finish can vary significantly, depending on the Internet traffic. I can count the seconds spent by the entire process and then set up the wait time at a security level, but it remains critical. Thanks, Franco.
    Saturday, October 10, 2015 9:12 PM
  • I add a small delay (wait) inside the for loop after the refresh.

    I don't think the wait time doesn't have to be relative to the query time because by setting background=false you are forcing process to be synchronous. Can you verify Gil?

    Saturday, October 10, 2015 11:24 PM
  • That is correct. The waiting time doesn't depend on the refresh time. We will consider improving this experience in the future.
    • Edited by Gil RavivMVP Saturday, October 10, 2015 11:35 PM
    Saturday, October 10, 2015 11:34 PM
  • I'm little confused now. Gil, you suggested me as a workaround to force all connection to NON background refresh and I did my tests in this way. But Intrasight and you are referring to background refresh again. So, waiting for a effective solution, what could be the correct settings for my problem in the meantime? Thanks, Franco.
    Sunday, October 11, 2015 5:38 AM
  • Hi Franco, You should avoid background refresh, and follow my suggestion above to add at the end of your macro a DoEvents and apply a wait operation which is not dependent on the actual refresh time. Intrasight suggested to apply the wait inside the loop, after each connection refresh, but this is not necessary. It is sufficient to perform the DoEvents and Wait at the end of your code, before you close the workbook. Hope it is clear now :)
    Sunday, October 11, 2015 6:07 AM
  • Ok, thank you Gil. It's what I did yesterday and it works fine. :-)
    Sunday, October 11, 2015 6:49 AM
  • Hi Gil, here I am again, sorry. Unfortunately I noticed that the query refresh process sometimes finishes and sometimes not. Same routine, same data, I didn't change anything. It seems to me that Application.Wait suspends also the refresh of the queries and then it is useless.

    Right now I'm testing a Function found on Internet that doesn't suspend every Excel activity as Application.Wait does, but introduces a timing delay between two lines of the current routine. I put it just before saving and closing the workbook. It seems to work, for the moment, but I'll do a lot of tests to be sure.

    Here it is, what do you think about it?

    Public Function Pause(NumberOfSeconds As Variant)
    On Error GoTo Err_Pause

        Dim PauseTime As Variant, start As Variant

        PauseTime = NumberOfSeconds
        start = Timer
        Do While Timer < start + PauseTime
        DoEvents
        Loop

    Exit_Pause:
        Exit Function

    Err_Pause:
        MsgBox Err.Number & " - " & Err.Description, vbCritical, "Pause()"
        Resume Exit_Pause

    End Function


    Monday, October 12, 2015 8:12 AM
  • Hi Franco, Can you use this code instead of your pause method?
    Application.Wait Now + TimeValue("0:00:05")
    Call for it at the end of all the refresh calls, and after a single DoEvents.
    Monday, October 12, 2015 5:24 PM
  • Hi Gil, I used your code yesterday, but it didn't work for me. As I wrote in my previous message, it seems that Application.Wait suspends all processes, also the refresh. I made visible the workbook and could see the refresh process truncated. This doesn't happen with Pause (10).

    In fact, it's also written on Application.Wait Method Page:

    "The Wait method suspends all Microsoft Excel activity and may prevent you from performing other operations on your computer while Wait is in effect. However, background processes such as printing and recalculation continue."

    "...background processes continue", but my routine doesn't work in background, as you suggested.

    Thanks for the time, Franco

    Monday, October 12, 2015 6:36 PM
  • There shouldn't be a refresh happening in the background if you set BackgroundQuery=False

    Is your code something like

    If lTest > 0 Then
        cn.Refresh BackgroundQuery:= False
        Application.Wait DateAdd("s", 1, Now)
    End If

    Monday, October 12, 2015 6:45 PM
  • Hey Franco, this is Jeff, Gil asked me to jump in and see if I can help.  Would you mind posting the code (or a small repro) here so we are on the same page?
    Monday, October 12, 2015 8:17 PM
  • My code is

    For Each cn In xlApp.Workbooks("EuropeListing.xlsm").Connections
    lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1")
    If Err.Number <> 0 Then
        Err.Clear
        Exit For
    End If
    If lTest > 0 Then cn.Refresh
    Next cn

    DoEvents
    xlApp.Wait DateAdd("s", 10, Now)

    'salva e chiudi la cartella dei Listini
    xlApp.Workbooks("EuropeListing.xlsm").Close (True)

    As you can see, I don't use "BackgroundQuery:= False" but, following Gil's suggestions, I unchecked "Enable background refresh" in Connection properties, didn't use any delay inside the loop but only one delay immediatly after DoEvents. This way didn't work, but replacing DoEvents and xlApp.Wait with Pause (10), it seem to work fine.


    Monday, October 12, 2015 8:33 PM
  • Hi Jeff, you are welcome, I just posted my code with some explanation. Thanks in advance, Franco
    Monday, October 12, 2015 8:35 PM
  • Thanks Franco, you are so right.  Application.Wait is blocking the polling that updates the status.  The Pause method is a reasonable solution, but here is a simpler alternative.

    Sub RefreshEmAndClose()     For Each cn In Application.Workbooks("MacroBook.xlsm").Connections      lTest = InStr(1, cn.OLEDBConnection.Connection, "provider=Microsoft.Mashup.OleDb.1")      If Err.Number <> 0 Then          Err.Clear          Exit For      End If      If lTest > 0 Then cn.Refresh      Next cn      Application.OnTime DateAdd("s", 10, Now), "CloseIt"    End Sub

    Sub CloseIt()      Application.Workbooks("MacroBook.xlsm").Close (True) End Sub


    Tuesday, October 13, 2015 5:33 AM
  • Hi Jeff, first of all, thanks for the confirmation that Application.Wait wasn't a good solution. About the alternative you offer, yes, it's simpler that mine and I'll test it soon. But let me remember that both solutions are workarounds. May we hope that you, at Microsoft, will solve the issue of automatic refresh in a more efficient and stable way, in order to optimize the execution of any routine?

    Thanks again for your help, Franco.

    Tuesday, October 13, 2015 6:15 AM
  • Yes, I totally agree, these are only workarounds.  We are looking at what we can do to make this work in a solid deterministic way.

    I appreciate your feedback.

    Tuesday, October 13, 2015 7:44 AM
  • Very Well! May I ask you to be so kind to let me know when a definitive solution will be available, so that I can adapt my routines?

    Thanks in advance, Franco.

    Tuesday, October 13, 2015 8:18 AM
  • Sorry, we don't have a timeline for this. It isn't booked yet, just talking and planning.

    Tuesday, October 13, 2015 8:45 AM
  • Yes, I understand that and I'm not in a hurry. In short, you mean that my only chance is to periodically test whether my routine works without the workaround or not yet, isn't? :-)

    Well, since you appreciated my feedback, in the meantime I'll open a new thread on Power Queries, regarding import tables from web pages. There are some kind of tables that Power Queries doesn't recognize, while Openoffice Calc and Google Sheets do. See you soon.

    Tuesday, October 13, 2015 9:27 AM
  • Excellent, thanks for taking the time to open the other issue too.
    Tuesday, October 13, 2015 4:21 PM
  • Hello, can anyone tell me if this issue has been fixed please? Thanks.

    Wednesday, February 7, 2018 4:48 PM