Range and Variant Array

  • sparrow

    sparrow - 2012-07-18

    Hi all

    I want to mimic this VBA code in Java using JACOB:

    Dim myArray As Variant
    myArray = Worksheets("Sheet1").range("B2:C17")

    I can't work out how to get JACOB to return the Variant array. Returning a single Variant is simple enough, but this has me stumped.

    Any help appreciated.

  • sparrow

    sparrow - 2012-07-19

    OK, another day's research leads me to SafeArrays.
    Has anyone passed a SafeArray from Excel to Java using a JACOB SafeArray?

    What I'm trying to do is the following:
    The user is going to select a range of cells, click a button in the toolbar and I need to pass that selected range back to Java as an array for processing.
    The reason for using an array is that we need to read each value in each cell of the selected range. Using something like Application.getSelection() works, but reading the Variant from each cell individually is incredibly resource intensive.

    Any help appreciated.

  • motron

    motron - 2012-07-19

    VBA :
    Property Range(Cell1, ) As Range
        Element von Excel.Worksheet

    I assume you will get a Dispatch for the range to work further on it. (no array of whatever)

    from com.jacob.samples.office.ExcelDispatchTest.main(String):

    Dispatch sheet = Dispatch.get(workbook, "ActiveSheet").toDispatch();
    Dispatch a1 = Dispatch.invoke(sheet, "Range", Dispatch.Get,
    new Object { "A1" }, new int).toDispatch(


    so try
    Dispatch myArrayRange = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object { "B2", "C17" }, new int).toDispatch();
    Dispatch.put(myArrayRange , "Value", "3.456");

    have fun

  • sparrow

    sparrow - 2012-07-19

    Thanks for the reply. Google eventually led me to these two pages:


    Object cell = Dispatch.invoke(sheet,"Range",Dispatch.Get,new Object {"A1:D1000"},new int).toDispatch();
    SafeArray array = Dispatch.get(cell,"Value").toSafeArray();


      SafeArray a = worksheet.UsedRange.Value
      SafeArray sA = new SafeArray(Variant.VariantString, new int  {0},new int  {….});

    Using a combination of those, I worked out that I can do the following:

            SafeArray array = null;
                array = Application.getSelection().getValue().toSafeArray();
            catch (ComFailException e)
                /* Only 1 cell has been selected. */
            if (array != null)
                Log.debug("dim=" + array.getNumDim());
                Log.debug("start row=" + array.getLBound(1));
                Log.debug("start col=" + array.getLBound(2));
                Log.debug("end row=" + array.getUBound(1));
                Log.debug("end col=" + array.getUBound(2));

    We have written some wrappers, so:
    Application.getSelection returns the selected range.
    getValue returns the Variant of the range. This is a Variant array of some sort when a range of cells is selected. I've not looked at what is returned in any detail yet.
    toSafeArray is JACOB code in com.jacob.com.Variant.toSafeArray(). It requires an array of Variants otherwise it throws ComFailException.

    That's where I'm up to right now. Next up is working through the array to see if it contains what I need. :-)


Log in to post a comment.