How do I retrieve result of a macro?

[aurell]

I have a project that used Microsof.Office.Interop to call VBA macros and retrieve the result, which was of clsWorkResult type (defined as a class in VBA). In C# I could have read the result like this:

dynamic workResult = wordApp.Run(macroName, documentPath);

Yet when I try to use it with NETOffice I get an exception:

[13:56:22]Class 00000000-0000-0000-0000-000000000000:clsWorkResult not found in loaded NetOffice Assemblies
Currently loaded NetOfficeApi Assemblies
Loaded NetOffice Assembly:System.Guid[] WordApi, Version=1.7.3.0, Culture=neutral, PublicKeyToken=dd9255e6bcd130c8
Loaded NetOffice Assembly:System.Guid[] OfficeApi, Version=1.7.3.0, Culture=neutral, PublicKeyToken=7c1c3e9d16cace88
Loaded NetOffice Assembly:System.Guid[] VBIDEApi, Version=1.7.3.0, Culture=neutral, PublicKeyToken=a3637beacf571e8a

   at NetOffice.Core.GetFactoryInfo(Object comProxy)
   at NetOffice.Core.CreateObjectFromComProxy(COMObject caller, Object comProxy)
   at NetOffice.WordApi._Application.Run(String macroName, Object varg1)
   at WK.Ladon.Automat.Preformatowanie.Worker.RunMacro(String macroName, String documentPath, RaportSpojnosciResult raport)

Can I retrieve complex types from VBA macros? How can I do this?

[jozefizso]

This is currently unsupported as NetOffice tries to create the instance of the type and it does not know about types defined in macros.

[A9G-Data-Droid]

I have a strange method I use to pass a parameter array to and Excel macro. The .Run command accepts an arbitrary number of arguments. I found that I can pass my macro name and all of the arguments to .InvokeMember("Run" and then my code magically can accept an arbitrary number of arguments too! In testing, I found my method also returns whatever came back from Excel as an object. So if you know what the thing is you can cast it to the expected type or begin some Type divination wizardry.

@jozefizso if you just return an object you can let us users deal with that because we likely know what type we have returned to ourselves from VBA land. I would still need my strange invoker to handle arbitrary length argument arrays, unless you have a solution to that problem for me.

I developed this technique back with the original Microsoft library and it works in NetOfficeFw too!

Looks like this:

        ///      <summary>
        ///      Run an Excel macro silently in the background. Quits at the end.
        ///      </summary>
        ///      <param name="excelFileName">Full path to Excel file.</param>
        ///      <param name="macroName">The macro you want to run.</param>
        ///      <param name="args">Arguments to pass into that macro procedure.</param>
        private static object ExcelRun(string excelFileName, string macroName, params string[] args)
        {
            // Make an array of object of string for the invoker
            var objectArray = new object[args.Length + 1];
            objectArray[0] = macroName;
            args.CopyTo(objectArray, 1);

            object runReturned;

            Application excelApp = default;
            Workbook thisWorkbook = default;
            try
            {
                // Create new Excel instance
                excelApp = new Application()
                {
                    Visible = false,
                    EnableEvents = false   // Suppress the Workbook_Open event
                };

                // Open the Excel workbook
                Workbooks excelBooks = excelApp.Workbooks;
                thisWorkbook = excelBooks.Open(excelFileName);

                const BindingFlags invokeMethod = BindingFlags.Default | BindingFlags.InvokeMethod;
                runReturned = excelApp.GetType()
                    .InvokeMember("Run", invokeMethod, null, excelApp, objectArray, CultureInfo.InvariantCulture);

                Console.WriteLine(runReturned);
            }
            finally
            {
                if (thisWorkbook != null)
                {
                    thisWorkbook.Close(false);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }
            }

            return runReturned;
        }