How to call from C# a basic macro defined in a calc document?

[bertrandgajac]

I have written a C# program that opens a LibreOffice calc file, then writes data inside the file and then saves the file. The calc file contains a basic macro called MettreEnPage that is used to define the layout of the data. I want that my C# program calls the basic macro MettreEnPage. The piece of code is the following.

            Object aFuncInst = oServMan.createInstance("com.sun.star.sheet.FunctionAccess");
            unoidl.com.sun.star.sheet.XFunctionAccess xFuncAcc = (unoidl.com.sun.star.sheet.XFunctionAccess)aFuncInst;
            uno.Any[] aArgs = new uno.Any[1];
            aArgs[0] = new uno.Any(0);
            uno.Any aResult = xFuncAcc.callFunction("MettreEnPage", aArgs);

There is an exception when the function xFuncAcc.callFunction is called. The exception gives no information.
My questions are the following:

• is it possible to call a basic macro defined in a calc file from a C# program ?
• If yes, what is the solution ?

Thank you for your help.

[jimk]

FunctionAccess is for Calc spreadsheet formulas, not for Basic macros.

To call a Basic subroutine, use the dispatcher to execute a macro:/// command.

dispatcher = ctx.ServiceManager.createInstanceWithContext(
    'com.sun.star.frame.DispatchHelper', ctx)
macro_call = ('macro:///Standard.Module1.Macro1("%s")' % url)
dispatcher.executeDispatch(frame, macro_call, "", 0, ())

See my answer at https://stackoverflow.com/a/35777552/5100564 for the complete Python example.

[bertrandgajac]

Thank you Jim K for your answer.
It works correctly. I have tried your solution described in Python in your answer and also the third solution in your stackoverflow link. Both solutions work well.
As it may help other people, below is the complete code of a small C# program that implements both solutions, depending on the variable methode_appel.

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using unoidl.com.sun.star.uno;
using unoidl.com.sun.star.lang;
using unoidl.com.sun.star.frame;
using unoidl.com.sun.star.beans;
using unoidl.com.sun.star.sheet;
using unoidl.com.sun.star.container;
using unoidl.com.sun.star.table;
using unoidl.com.sun.star.text;


namespace test_appel_libre_office_avec_macro
{
    class Program
    {
        static void appel()
        {
            string date_du_jour = System.DateTime.Now.ToString();
            string date_pour_fic = date_du_jour.Substring(6, 4) + date_du_jour.Substring(3, 2) + date_du_jour.Substring(0, 2) + date_du_jour.Substring(11, 2) + date_du_jour.Substring(14, 2) + date_du_jour.Substring(17);
            string prefixe = "pref";
            string nom_fic_sortie = prefixe + date_pour_fic.Replace("/", "") + ".xlsm";
            // creation du fichier
            string variableEntornoPATH = Environment.GetEnvironmentVariable("PATH");
            Environment.SetEnvironmentVariable("PATH", variableEntornoPATH + ";C:\\Program Files\\LibreOffice\\program;");
            XComponentContext ctx = uno.util.Bootstrap.bootstrap();
            XMultiServiceFactory srv_manager = (XMultiServiceFactory)ctx.getServiceManager();
            XComponentLoader component_loader = (XComponentLoader)srv_manager.createInstance("com.sun.star.frame.Desktop");
            string url = @"private:factory/scalc";
            string nom_fic_excel = "nom_fic";
            XComponent component = component_loader.loadComponentFromURL(url, nom_fic_excel, 0, new PropertyValue[0]);
            // ecriture dans le fichier
            XSpreadsheetDocument document = (XSpreadsheetDocument)component;
            XSpreadsheets feuilles = document.getSheets();
            XIndexAccess index_feuilles = (XIndexAccess)feuilles;
            XSpreadsheet feuille = (XSpreadsheet)index_feuilles.getByIndex(0).Value;
            XCell cell1 = feuille.getCellByPosition(0, 0);
            ((XText)cell1).setString("titre");
            XCell cell2 = feuille.getCellByPosition(0, 1);
            ((XText)cell2).setString("val");

            // sauvegarde
            PropertyValue[] propVals = new PropertyValue[3];
            propVals[0] = new PropertyValue();
            propVals[0].Name = "FilterName";
            propVals[0].Value = new uno.Any("Text - txt - csv (StarCalc)");   // new uno.Any("calc8");
            propVals[1] = new PropertyValue();
            propVals[1].Name = "FilterOptions";
            propVals[1].Value = new uno.Any("9,0,76,1,,0,false,true,false");
            propVals[2] = new PropertyValue();
            propVals[2].Name = "Overwrite";
            propVals[2].Value = new uno.Any(true);
            string nom_sauv = "file:///C:/temp/" + nom_fic_sortie;
            XStorable doc = (XStorable)document;
            doc.storeAsURL(nom_sauv, propVals);

            // appel de la macro Basic
            string appel_macro = "macro:///Standard.ModeleSimple.MettreEnPage()";
            int methode_appel = 1;
            if (methode_appel == 0)
            {
                // appel en C#
                XModel controller = (XModel)component;
                XFrame frame = controller.getCurrentController().getFrame();
                XDispatchHelper dispatch_helper = (XDispatchHelper)srv_manager.createInstance("com.sun.star.frame.DispatchHelper");
                dispatch_helper.executeDispatch((XDispatchProvider)frame, appel_macro, "", 0, new unoidl.com.sun.star.beans.PropertyValue[0]);
                doc.storeAsURL(nom_sauv, propVals);
            }
            else
            {
                // appel par ligne de commande
                string nom_complet = "c:\\temp\\" + nom_fic_sortie;
                string cmd = "\"c:\\program files\\libreoffice\\program\\soffice\"";
                string args = appel_macro + nom_complet;
                ProcessStartInfo psi = new ProcessStartInfo(cmd);
                psi.Arguments = args;
                System.Diagnostics.Process.Start(psi);
            }
        }
        static void Main(string[] args)
        {
            appel();
        }
    }
}