none
PQ can't failed to refresh data, "External Data Connections have been disabled." RRS feed

  • Question


  • I use macro to auto open, refresh the query but I can't load any data from external file in to PQ.

    On the "workbooks query" panel which listed all the queries. I've got error "download did not complete" and when I put the mouse over that error, it shows "External Data Connections have been disabled."

    However this file can be access using macro or vlookup.

    I already add this excel sheet to trusted location to avoid data content warning.

    Please kindly recommend some configuration that I may missed.

    Thank you.

    Monday, May 11, 2015 4:00 AM

Answers

All replies

  • When you open the workbook, do you still see the yellow warning bar at the top of Excel about connections being disabled?
    Monday, May 11, 2015 11:33 PM
  • Thank you. After I set the security setting at "trust center" -> "external content" -> "enable all connections"

    The error "External Data Connections have been disabled" is gone.

    However, I still have problem "download did not complete" when refresh the file using VBA.

    Some comment mentioned about this code, but wasn't work. Do you have any idea?

        For Each objConnection In ThisWorkbook.Connections
            'Get current background-refresh value
            bBackground = objConnection.OLEDBConnection.BackgroundQuery
    
            'Temporarily disable background-refresh
            objConnection.OLEDBConnection.BackgroundQuery = False
    
            'Refresh this connection
            objConnection.Refresh
    
            'Set background-refresh value back to original value
            objConnection.OLEDBConnection.BackgroundQuery = bBackground
        Next

    Tuesday, May 12, 2015 4:28 AM
  • Yes, this is an issue with the UI status reporting when refreshing data through VBA -- your data should still be correctly refreshed in the worksheet.
    Tuesday, May 12, 2015 11:17 PM
  • Hi, I have the same issue here.

    I also tried mentioned code in order to disable the background refreshing, so I'm able to wait for the connection to be ready refreshing and display a message that it is finished. However, when background refreshing is turned on, the query runs fine and data is loaded to the worksheet. However, when I turn off the background refresh, the query mentiones: "Download Failed".

    When I run the code manually and keep pressing F5 on error then in the end (after three error messages) it has downloaded the data. But when I add "On Error Resume Next" the it doesn't download the data.

    Also when I manually disable background query and click Refresh All then I get the same error.

    Does anyone have an idea?

    I can't upload pictures due to new account, but if checked I will add some print screens.

    How can my account be verified?? I'm not able to leave my email in my profile.....


    • Edited by Niditro Tuesday, December 17, 2019 8:58 AM
    Monday, December 16, 2019 3:56 PM