Terry Higginbotham

Terry Higginbotham

  • 1.9k
  • 24
  • 141

Refresh external datalinks in excel

Oct 23 2019 2:50 PM
The code below doesn't seem to refresh the data links. Does anyone see the issue?
  1. Excel.Application xlApp2 = new Excel.Application();  
  2. xlApp2.DisplayAlerts = false;  
  3. xlApp2.Visible = true;  
  4. Excel.Workbook xlWorkbookt2 = xlApp.Workbooks.Open(@ "\\twg-jks-fs01\twg\Sales\Sales_Volume\Consolidated Sales Volume\Daily Note\Auto DOS Files\Go Live\TWG DOS - FY19 Std Rpts Automated HTML Ready.xlsx", 3, false, missing, missing, missing, true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, missing, true, missing, missing, missing, missing, missing);  
  5. Excel._Worksheet xlWorksheet2 = xlWorkbookt2.Sheets[1];  
  6.   
  7. foreach(Microsoft.Office.Interop.Excel.WorkbookConnection conn in xlWorkbookt2.Connections) {  
  8.  Console.WriteLine(conn.Name);  
  9. }  
  10. Excel.Range xlRange2 = xlWorksheet2.UsedRange;  
  11. object misValue2 = System.Reflection.Missing.Value;  
  12. mWorkSheets = xlWorkbookt2.Worksheets;  
  13. string text = File.ReadAllText(@ "\\twg-jks-fs01\twg\PublishedApps\DSN\DBTemplate.txt", Encoding.UTF8);  
  14. string buttons = string.Empty;  
  15. string frames = string.Empty;  
  16.   
  17. int cntr = 0;  
  18. try {  
  19.  xlWorkbookt2.RefreshAll();  
  20.  System.Threading.Thread.Sleep(60000);  
  21.   
  22.  xlApp2.Application.CalculateUntilAsyncQueriesDone();  
  23.  xlWorkbookt2.Save();  
  24. catch (Exception ex) {  
  25.  Console.WriteLine("failed Refreshing XLSX Links 2");  
  26. }  

Brought to you by: JavaScript SDK for Bold BI dashboard and analytics embedding. Free trial.