none
Microsoft.Office.Interop.Excel.Workbook.RefreshAll Method not working in C# RRS feed

  • Question

  • Hi,

    We have developed a windows service in Visual Studio using C# which will refresh the excel reports.

    The below line is used for the Excel workbook Refresh. But this alone is not working and the excel workbook is not refreshed.

    Microsoft.Office.Interop.Excel.Workbook.RefreshAll()

    Can you please help with a workaround or solution to rectify this issue?

    Thanks,

    Thanan

    Note:

    Actual code snippet is given below. ("excelWorkbook.RefreshAll();" is the exact line which isn't working...)

    private void refreshSingleReport(string ReportId, string TrackName, string ReportName, string Password, string SourcePath, string Macro, string Month)

            {

                try

                {

                    //Get the file paths

                    string MasterRootPath = ConfigurationManager.AppSettings["MasterRootPath"].ToString();

                    string WorkingRootPath = ConfigurationManager.AppSettings["WorkingRootPath"].ToString();

                    string RefreshedRootPath = ConfigurationManager.AppSettings["RefreshedRootPath"].ToString();

                    string MasterFilePath = MasterRootPath + SourcePath + "\\" + ReportName;

                    string WorkingFilePath = WorkingRootPath + SourcePath + "\\" + ReportName;

                    string RefreshedFilePath = RefreshedRootPath + SourcePath + "\\" + ReportName;

                    //Copy the report from Master location to working folder

                    if (!System.IO.Directory.Exists(WorkingRootPath + SourcePath))

                    {

                        System.IO.Directory.CreateDirectory(WorkingRootPath + SourcePath);

                    }

                    System.IO.File.Copy(MasterFilePath, WorkingFilePath, true);

                   

                    //Refresh the Report

                    object misValue = System.Reflection.Missing.Value;

                    Excel.Application excelApp = new Excel.Application();

                    //excelApp.Visible = true; 

                    excelApp.DisplayAlerts = false;   //Disables Display Alerts that might pop up while refreshing a report

                    Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(WorkingFilePath,

                                                    misValue, misValue, misValue, Password, Password, misValue, misValue, misValue,

                                                    misValue, misValue, misValue, misValue, misValue, misValue);

                    Excel.Sheets excelSheets = excelWorkbook.Worksheets;

                    if (ConfigurationManager.AppSettings["IsRequiredAutoRefreshSheet"].ToString().ToUpper() == "YES")

                    {

                        //Updating month in AutoRefresh Sheet

                        string currentSheet = "AutoRefresh";

                        Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);

                        Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range("RefreshMonth", misValue);

                        excelCell.Formula = Month;

                    }

                    excelWorkbook.RefreshAll();

                   

                    excelApp.CalculateUntilAsyncQueriesDone();

                   

                    excelWorkbook.Save();

                    //RUN MACRO IF PRESENT

                    if (Macro.Length != 0)

                    {

                        excelApp.Run(Macro);

                        excelApp.CalculateUntilAsyncQueriesDone();

                        excelWorkbook.Save();

                    }

                    excelWorkbook.Close(misValue, misValue, misValue);

                    excelApp.Quit();

                    GC.Collect();

                    GC.WaitForPendingFinalizers();

                    Marshal.FinalReleaseComObject(excelWorkbook);

                    Marshal.FinalReleaseComObject(excelApp);

                   

                                    

                    //Copy the refreshed report from working folder to Refreshed folder

                    if (!System.IO.Directory.Exists(RefreshedRootPath + SourcePath))

                    {

                        System.IO.Directory.CreateDirectory(RefreshedRootPath + SourcePath);

                    }

                    System.IO.File.Copy(WorkingFilePath, RefreshedFilePath, true);

                                                }

                catch (Exception ex)

                {

                    string error = ex.Message;

                }

            }

    • Moved by CoolDadTx Wednesday, September 24, 2014 3:47 PM Office related
    Wednesday, September 24, 2014 12:44 PM

Answers