How to execute a "Refresh All" for Excel programmatically in C#?

Question

I've written an application to read in some data from an Excel spreadsheet using an oledbconnection.  Some of my data is from an external datasource which is why I would need to refresh the data before reading it.

My external data source is not a database, but an asp page and I'm not sure how to create an oledbconnection to an asp page, but I do know how to create an external connection from Excel to the datasource.

It is possible to run an excel "refresh all" from my c# application?

Answer 1

Yes it's possible.

Code Snippet

1. private void _DataRefresh(String S_Directory)
2. {
3.     if (S_Directory.Substring(S_Directory.Length - 1, 1) != @"\")
4.     {
5.         S_Directory = S_Directory + @"\";
6.     }
7.  
8.     DirectoryInfo finfo = new DirectoryInfo(S_Directory);
9.  
10.     foreach (FileInfo fileName in finfo.GetFiles("*.xls*"))
11.     {
12.         if (isOpen(S_Directory + fileName) && fileName.ToString().Substring(1, 2) != "~$")
13.         {
14.             try
15.             {
16.                 xls.Application ExcelObj = new xls.Application();
17.                 ExcelObj.DisplayAlerts = true;
18.                 ExcelObj.Visible = true;
19.                 ExcelObj.DefaultFilePath = S_Directory;
20.                 
21.                 xls.Workbook eBook = ExcelObj.Workbooks.Open(fileName.ToString(), false, false,
22.                     Type.Missing, "", "", true, xls.XlPlatform.xlWindows, "",
23.                     false, false, 0, false, true, 0);
24.  
25.                 foreach (xls.WorkbookConnection wc in eBook.Connections)
26.                 {
27.                     if (wc.Type.ToString() == "xlConnectionTypeODBC")
28.                     {
29.                         wc.ODBCConnection.BackgroundQuery = false;
30.                     }
31.                     else
32.                     {
33.                         wc.OLEDBConnection.BackgroundQuery = false;
34.                     }
35.                 }
36.  
37.                 eBook.RefreshAll();
38.                 eBook.Save();
39.                 NAR(eBook);
40.                 ExcelObj.Workbooks.Close();
41.                 ExcelObj.Quit();
42.                 NAR(ExcelObj);
43.             }
44.             catch (COMException e)
45.             {
46.                 ErroredFiles.Add(fileName.ToString());
47.                 WriteErrorMessage(S_Directory, fileName.ToString(), e.Message.ToString());
48.             }
49.         }
50.  
51.         else { }
52.     }
53.  
54.     KillpIDs("Excel");
55.  
56.     if (ErroredFiles.Count > 0)
57.     {
58.         GenerateEmail("Data Refresh Failures");
59.     }
60. }

Answer 2

That was quick.

Is there a way to reuse the following code I've written to connect to my Excel file, or can Refresh all not be used with these data types?

// Create a connection to excel spreadsheet

 

string connectionString = null;

 

OleDbConnection cnn;

connectionString =

@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Test.xls;Extended Properties=Excel 8.0;";

cnn =

new OleDbConnection(connectionString);

Answer 3

The Refresh All is part of the client code, so I believe you'd need to use the Excel PIA to accomplish that.

Answer 4

Thanks for the clarification.