Excel 2007 or 2010 must be installed on the same computer that will host required DLL and used by C# program
Install PrimaryInteropAssembly.exe (DLL), depending Office version :
Office 2007 : http://www.microsoft.com/download/en/details.aspx?id=18346
Office 2010 : http://www.microsoft.com/download/en/details.aspx?id=3508
1) Create a new project Console Application C#
2) Add reference called Microsoft.Office.Interop.Excel, previously installed in COM section in project
3) Right click on project, Add New Item and copy this source :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Microsoft.Office.Interop.Excel;
namespace RefreshExcel
{
class RefreshExcel
{
static void Main(string[] args)
{
try
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.DisplayAlerts = false;
excelApp.Visible = true;
string workbookPath = args[0];
Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value);
foreach (PivotCache cache in excelWorkbook.PivotCaches())
{
cache.BackgroundQuery = false;
}
excelWorkbook.RefreshAll();
excelWorkbook.SaveAs(workbookPath);
excelWorkbook.Close(false, workbookPath, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
excelWorkbook = null;
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
Console.WriteLine(String.Format("Success : {0} updated", workbookPath));
}
catch (Exception ex)
{
Console.WriteLine(String.Format("Failure : {0}", ex));
}
}
}
}
4) Compile project
For those who do not have Visual Studio, compiled application can be downloaded below.
And finally to test in DOS mode, you can do like this :
C:\>RefreshExcel.exe C:\test.xlsx
If the C # program is executed with a SQL job (batch or SSIS), this error message can occur :
Microsoft Office Excel: Microsoft Office Excel cannot access the file '<location>\<filename>.xlsx'.
There are several possible reasons:
- The file name or path does not exist.
- The file is being used by another program.
- The workbook you are trying to save has the same name as a currently open workbook.
To fix this problem, just create a folder called "Desktop" in :
Windows 2008 Server (x64) : C:\Windows\SysWOW64\config\systemprofile
Windows 2008 Server (x86) : C:\Windows\System32\config\systemprofile
For more explanation, see :
http://support.microsoft.com/kb/257757