none
Excel freezes when refreshing a query where credentials are needed RRS feed

  • Question

  • Hi all,

    I hope someone can help me with this. I have hundreds of workbooks (one per cost center) that connect to an Excel file on a SharePoint online site to get the latest data. To refresh a workbook, one has to click a button in their workbook that triggers some VBA to refresh all the queries in their workbook.

    That all works fine, IF the user has previously connected to the SharePoint. If they have connected manually before, they connected through a window called "Access web content" (like the one in the screenshot below) using their organizational O365 account. 

    If they just click the button without previously being connected, this window "Access web content" does not pop up and Excel freezes.
    My goal is that even if the have not yet connected manually, Excel doesn't freeze and the pop up "Access web content" appears when the users click the VBA refresh button in the cost center workbook.
    Anybody out there that might be able to help?
    Thanks so much.
    Robert
    Here is the code I am using:
    Public Sub Refresh_DB()
           
    Application.DisplayAlerts = False
      ActiveWorkbook.Queries.FastCombine = True
     
      ' Make connections refresh synchronously
      For Each conn In ActiveWorkbook.Connections
          If conn.RefreshWithRefreshAll Then
              If Not IsNull(conn.OLEDBConnection) Then
                  conn.OLEDBConnection.BackgroundQuery = False
              End If
          End If
      Next conn
    ActiveWorkbook.RefreshAll
    Application.CalculateUntilAsyncQueriesDone
    '....       
    End Sub

     



    Wednesday, September 11, 2019 1:52 PM

All replies