none
Excel dashboard getting stuck

    Question

  • Hi guys,

    I continue facing this same problem: my dashboard gets stuck after some hours of running.

    It throws the following message: "Cannot find the text file to update the external data range. Control if the text file was renamed, moved and then repeat the update "



    Of course the .txt file is perfectly fine and reachable inside a shared network drive.

    After it gets stuck, If I close excel, I see an Excel process still running... If I close it and then reopen the dash everything works fine..... For another 8 hours or so. Then everything back again.

    Here's the macro. 

    Public i As Integer
    
    Public Sub loop()
    
    Dim wb As Workbook
    Dim ws As Worksheet
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = False
    
    
    
    'On Error Resume Next
     
    
      Set wb1 = Workbooks(1)
      Set wb2 = Workbooks(2)
      
      
      
    
      If i Mod 3 = 0 Then
      
        wb1.RefreshAll
           
        wb1.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
        
        DoEvents
        
        wb2.RefreshAll
        
        DoEvents
        
      End If
      
             
            For Each ws In wb1.Worksheets
            
               If ws.Visible = xlSheetVisible Then
                
                ws.Activate
               DoEvents
                   
                Application.Wait (Now + TimeValue("0:00:20"))
              
                         
              End If
                
          Next
     
      
    
     For Each ws In wb2.Worksheets
            
               If ws.Visible = xlSheetVisible Then
                
                ws.Activate
               DoEvents
                   
              Application.Wait (Now + TimeValue("0:00:10"))
             
                
               End If
         Next
    
        
     i = i + 1
     
     
        
    Application.OnTime Now + TimeValue("00:00:10"), "loop"
    
    End Sub
    
    Public Sub Go()
    
        i = 0
        
        Call loop
    
    
    End Sub

    Friday, April 19, 2019 8:55 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Monday, April 22, 2019 3:26 AM
  • Ok thank you.
    Tuesday, April 23, 2019 6:25 AM