/*****************************************************************************
*                                                                            *
*   XLCONNECT.SPL Copyright (C) 2008 DSP Development Corporation             *
*                               All Rights Reserved                          *
*                                                                            *
*   Author:      Randy Race                                                  *
*                                                                            *
*   Synopsis:    Establishes an ActiveX connection with Excel                *
*                                                                            *
*   Revisions:    6 May 2008  RRR  Creation                                  *
*                                                                            *
*****************************************************************************/


#if @HELP_XLCONNECT

    XLCONNECT

    Purpose: Establishes an ActiveX connection to EXCEL.

    Syntax:  XLCONNECT(new, server)

               new - Optional integer. Always create a new connection.

                       -1: use the current connect

                        0: use the current connection if it exists, else
                           create a new connection (default).

                        1: close the current connection if it exists and
                           create a new connection.

            server - Optional string, name of the Excel server. Defaults
                     to "Excel.Application".

    Returns: An object handle if successful.

    Example:
             excel = xlconnect();

             Variable excel is an ActiveX handle if a successful connection to
             Excel is established.

    Remarks:
             XLCONNECT attempts to connect to the running instance of Excel,
             otherwise XLCONNECT starts and connects to a new instance of Excel.

             If a connection was already established from a previous call to
             XLCONNECT, the existing connection is returned.

             XLCONNECT(1) establishes a new connection even if a current
             connection exists. The current connection is closed.

             XLCONNECT is used internally by Excel ActiveX I/O functions
             such as XLEVAL, XLEXEC, XLPUT and XLGET.

    See Also:
             Createobject
             Xleval
             Xlexec
             Xlget
             Xlput
#endif


static _Excel;

/* connect to Excel ActiveX server */
xlconnect(new, server)
{
        local xl, created;
        local defserver = "";

        if (argc < 2)
        {
                if (argc < 1) new = 0;

                server = "";
        }

        if (strlen(server) == 0)
        {
                server = "Excel.Application";
        }

        if (new > 0)
        {
                xldisconnect();
        }

        /* use existing Excel connection or establish a new one */
        if (new >= 0 && not(isobject(_Excel)))
        {
                /* primary server */
                (xl, created) = xlconnect_server(server);

                if (not(isobject(xl)))
                {
                        if (strlen(defserver) > 0)
                        {
                                /* default server */
                                server = defserver;
                                xl     = xlconnect_server(server);
                        }

                        if (not(isobject(xl)))
                        {
                                error(sprintf("xlconnect - cannot connect to %s", server));
                        }
                }
                else if (created)
                {
                        /* successfully created primary, hide it */
                        xl.visible = 0;
                }

                /* save Excel handle to static variable */
                _Excel =  xl;

                /* setup activex error handlers */
                xlinitatxerr();
        }
        else
        {
                /* get static Excel handle */
                xl = _Excel;
        }

        return(xl);
}


xlconnect_server(server, verbose)
{
        local xl, created = 0;

        if (argc < 2) verbose = 1;

        /* try connecting to a running instance */
        xl = getobject(server, 0);

        if (not(isobject(xl)))
        {
                /* try loading new instance of Excel */
                if (verbose)
                {
                        echo(sprintf("Connecting to Excel Server %s ...", server));
                }

                xl      = createobject(server, "", 0);
                created = 1;

                if (verbose)
                {
                        echo("");
                }

                if (isobject(xl))
                {
                        /* add workbook */
                        xl.workbooks.add();
                }
        }

        return(xl, created);
}


/* release Activex object */
xldisconnect()
{
        if (isobject(_Excel))
        {
                releaseobject(_Excel);
                _Excel = -1;
        }

        if (isfunc("xlresetatxerr"))
        {
                /* reset activex error handlers */
                xlresetatxerr();
        }
}