COM Automation / Running Excel Macros

Question

Hi,

Possibly, my questions do not presume a simple answer as I'm asking to share a specific experience.

1. What kind of issues you experienced trying to execute Excel macros through COM Automation? Are there any specific requirements for creation of macros to be run through Automation?

2. Can you share your experience with data (scalar and vector parameters) pass-in / pass-back for macros run through Automation?

I have tried some ideas already and it worked; but I'd like to find out if someone had gone through that way and what issues to expect - before I invest time and resources in building the framework. Finally, I will share here results of my investigation and decision made at the end.

Thank you, Albert Gareev

Answer 1

OK, this is what I have so far.

1) Issues. Every single exit point on COM Client should have proper disconnection routine so that Excel.Application is properly shut down and released. If COM Client was executed in debugging mode, and execution was manually stopped, then shut down Excel.Application manually too (from Task Manager).

2) I currently use worksheet to pass in parameters and retrieve the results.

Additionally, for a data parsing task executed within macro I pass-in text files names. The contents is read and normalized from the Excel macro side.

Thanks.