I am looping through a directory of several hundered excel files and trying to refresh the excel files one at a time. I keep getting this error which indicates that the refresh operation is still running on File A, for example, and FileB is trying to start a refresh operation. The loop is to fast and somehow I have to wait for the prior refresh operation on File A to complete before starting to refresh File B.
Unhandled Exception: System.Runtime.InteropServices.COMException: The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER)) at Microsoft.Office.Interop.Excel._Workbook.RefreshAll() at RefreshExcelFiles.Program.RefreshFile(String fileName)
Here is my code. How can I wait for the refresh operation to complete before starting to process a new file in the loop? Or, wait for the marshal release operation on the wb object to complete before starting on a new file?
using System;
using System.Configuration;
using System.IO;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
namespace RefreshExcelFiles
{
internal class Program
{
private static string _fileDirectory;
private static Application _excelApp;
private static void Main(string[] args)
{
_fileDirectory = ConfigurationManager.AppSettings["FileDirectory"];
_excelApp = new Application();
_excelApp.DisplayAlerts = false;
Console.WriteLine("starting to refresh files");
int i = 0;
int total = Directory.GetFiles(_fileDirectory).Length;
foreach (string file in Directory.GetFiles(_fileDirectory))
{
i += 1;
Console.WriteLine("File " + file + " " + i.ToString() + "/" + total.ToString());
RefreshFile(file);
}
_excelApp.Quit();
Marshal.FinalReleaseComObject(_excelApp);
Console.WriteLine("press any key to exit");
Console.ReadLine();
}
private static void RefreshFile(string fileName)
{
_Workbook wb = _excelApp.Workbooks.Open(fileName, false);
wb.RefreshAll();
wb.Save();
wb.Close(Type.Missing, Type.Missing, Type.Missing);
Marshal.FinalReleaseComObject(wb);
}
}
}
I found an answer to my problem. I needed to implement IMessageFilter RetryRejectedCall. For a C# and VB.NET code sample of using IMessageFilter::RetryRejectedCall, see this blog post (wayback machine archived link).
If you don't register a MessageFilter yourself (by calling CoRegisterMessageFilter), you will get default behavior which will be to fail the call if it gets rejected. .Net converts the failure HRESULT to an exception. To deal with the possibility of the server being busy when you try to call, you need to implement IMessageFilter::RetryRejectedCall in your client code and also register the message filter. In most cases, you will just need to wait for a few seconds and then retry the call--generally that will be sufficient time to enable Word to finish whatever it is doing so it can handle your call.
User contributions licensed under CC BY-SA 3.0