You may not be waiting long enough for the document to open before attempting to run the macro. Try running it from a separate button after you have called the Open method on the workbook. In addition, make sure that macros are enabled.
We have been seeing this error at one of the projects in our account. Currently, it uses RAS 8.0.1088.0.
We have tried the suggestion by Thomas, which solved the error partially. In some particular scenario, we still end up receiving the error even after having a 2 second pause in place.
Scenario Details are as follows:
The project uses macros extensively present in various Excel files. The macros, upon execution, generate variety of pop-ups, and usually we need to PerformClick on them. To achieve this, the DEV team has followed following approach:
Have a Windows adapter in 'MonitorAll = True' for EXCEL.EXE. This is to sense the Pop-ups and PerformClick on appropriate button.
Open the Workbook.
Use Parallel process block, on 1 thread execute RunMacro method of Excel Connector, and on other thread Use WaitForEvent for Created event of target popup screen control and click the button.
As per business needs, the flow having Parallel process block is repeated using for loop
Well, not really a right way to do things, but this is what the DEV team has done across the whole project. And unless we find out the exact cause, its difficult to convice them to give up on the above implementation.
We happen to be getting the 'Microsoft COM instance is not available' error, when the macro execution is done inside the loop. We have tried a workaround as follows and it works all the time:
Extract a local ExcelApplication proxy from Excel Connector right after Excel Workbook is opened.
Use a C# script for running the macro by using the above proxy as a parameter. (Using Application.Run(macroname, MissingValue, MissingValue))
Basically we have stopped using the ExcelConnector. This is also because, we also have been receiving a NullReferenceException while using GetCellValue(), SetCellValue(), ImportData(), ExportData() methods using ExcelConnector in the scenario mentioned above. Even for those methods, we came up with C# script based workarounds.
We may be wrong, but we think that the 'COM Instance' mentioned in the error is Excel Application Instance that Connector tries to get when the automation thread hits RunMacro or any other methods mentioned above. Due to some reason, it fails to get it. This may have been caused by the repeatative execution inside the loop, or the rThread running (matching process), hence the problem. We also think that having a Windows Adapter in 'MonitorAll' might have something to do with it, but its just a speculation. We badly need to know the root cause.
We can not attach the log file that contains the above exception, because it contains proprietary info. Will try to provide if we are able to regenerate with a sample solution.