Forum Guidelines

This forum is for finished source code that is working properly. If you have questions about this or any other source code, please post it in one of the Discussion Forums, not here.
See more
See less

Excel Com Numeric Column References using Resize

  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel Com Numeric Column References using Resize

    'Uses Excel "Resize" to avoid alpha column references when assigning an
    'array to a range
    #DIM ALL
    #COMPILE EXE "xlresize.exe"
    #INCLUDE ""
    FUNCTION PbcomXlResize (lRows AS LONG,lCols AS LONG) AS LONG
        DIM oExcelApp       AS ExcelApplication
        DIM oExcelWorkbook  AS ExcelWorkbook
        DIM vExcelWorkbook  AS VARIANT
        DIM oExcelWorkSheet AS ExcelWorkSheet
        DIM vExcelWorksheet AS VARIANT
        DIM vFirstRow       AS VARIANT
        DIM vFirstCol       AS VARIANT
        DIM oFirstCellRange AS ExcelRange
        DIM vFirstCellRange AS VARIANT
        DIM oOutRange       AS ExcelRange
        DIM vOutRange       AS VARIANT
        DIM vRows           AS VARIANT
        DIM vCols           AS VARIANT
        DIM vData           AS VARIANT
        DIM lRowCount       AS LONG
        DIM lColCount       AS LONG
        DIM vTrue           AS VARIANT
        ON ERR GOTO Terminate
        SET oExcelApp = ExcelApplication IN $PROGID_ExcelApplication8
           SET oExcelApp = NEW ExcelApplication IN $PROGID_ExcelApplication8
        END IF
        LET vTrue = 1
        OBJECT LET oExcelApp.Visible = vTrue
        OBJECT CALL oExcelApp.WorkBooks.Add TO vExcelWorkbook
        SET oExcelWorkbook = vExcelWorkbook
        OBJECT CALL oExcelWorkBook.WorkSheets.Add TO vExcelWorkSheet
        SET oExcelWorkSheet = vExcelWorkSheet
        DIM sArray (1 TO lrows, 1 TO lcols) AS STRING
        FOR lRowCount = 1 TO lrows
           FOR lColCount = 1 TO lcols
               sArray(lRowCount,lColCount) = STR$(lRowCount) & STR$(lColCount)
           NEXT lColCount
        NEXT lRowCount
        LET vFirstRow = 5
        LET vFirstCol = 5
        OBJECT GET oExcelWorkSheet.Cells(vFirstRow,vFirstCol) TO vFirstCellRange
        SET oFirstCellRange = vFirstCellRange
        LET vRows = UBOUND(sArray,1)
        LET vCols = UBOUND(sArray,2)
        OBJECT GET oFirstCellRange.Resize(vRows,vCols) TO vOutRange
        SET oOutRange = vOutRange
        LET vData = sArray()
        OBJECT LET oOutRange.Value = vData
        SET oExcelApp       = NOTHING
        SET oExcelWorkbook  = NOTHING
        SET oExcelWorkSheet = NOTHING
        SET oFirstCellRange = NOTHING
        SET oOutRange       = NOTHING
        FUNCTION = 0
       DIM lReturnValue AS LONG
       lReturnValue = PbcomXlResize(1000,5)

  • #2
    Hi John

    Nice trick! Here is a version with reusable macros:
    #dim all
    #compile exe "xlresize.exe"
    #include ""
    macro DEF_RANGE(Range, Row, Col, nRow, nCol, Sheet)
        macrotemp vRow, vCol, v, Range   
        local vRow as variant : vRow = Row
        local vCol as variant : vCol = Col
        local v as variant
        object get Sheet.Cells(vRow,vCol) to v
        set Range = v
        vRow = nRow
        vCol = nCol
        object get Range.Resize(vRow,vCol) to v
        set Range = v
    end macro
    macro SHOW_ARRAY(Arr,Row,Col, Sheet)
        macrotemp v, Range   
        local vArr as variant : vArr = Arr()
        local Range as ExcelRange
        DEF_RANGE(Range,Row,Col,ubound(Arr(1)),ubound(Arr(2)), Sheet)
        object let Range.Value = vArr
        set Range = nothing
    end macro
    function PbcomXlResize (lRows as long,lCols as long) as long
        dim oExcelApp       as ExcelApplication
        dim oExcelWorkbook  as ExcelWorkbook
        dim vExcelWorkbook  as variant
        dim oExcelWorkSheet as ExcelWorkSheet
        dim vExcelWorksheet as variant
        dim lRowCount       as long
        dim lColCount       as long
        dim vTrue           as variant
        on err goto Terminate
        set oExcelApp = ExcelApplication in $PROGID_ExcelApplication9
        if isfalse isobject(oExcelApp) then
           set oExcelApp = new ExcelApplication in $PROGID_ExcelApplication9
        end if
        let vTrue = 1
        object let oExcelApp.Visible = vTrue
        object call oExcelApp.WorkBooks.add to vExcelWorkbook
        set oExcelWorkbook = vExcelWorkbook
        object call oExcelWorkBook.WorkSheets.add to vExcelWorkSheet
        set oExcelWorkSheet = vExcelWorkSheet
        dim sArray(1 to lrows, 1 to lcols) as string
        for lRowCount = 1 to lrows
           for lColCount = 1 to lcols
               sArray(lRowCount,lColCount) = str$(lRowCount) & str$(lColCount)
           next lColCount
        next lRowCount
        SHOW_ARRAY(sArray, 5,5, oExcelWorkSheet)
        set oExcelApp       = NOTHING
        set oExcelWorkbook  = NOTHING
        set oExcelWorkSheet = NOTHING
    end function
    function pbmain
       PbcomXlResize 1000,5
    end function
    [email protected]

    [This message has been edited by Peter P Stephensen (edited December 04, 2002).]
    [email protected]


    • #3
      Another way to convert to excel cell Addresses
      INFO: Convert (row,col) Indices into Excel-Style A1:C1 Strings;en-us;q198144


      • #4
        For those who are curious about how to get
        or create the ""

        John once wrote...

        - Try: c:\PBCC30\samples\com\excel\
        - Or you can create your own using the com browser within
        - the pbcc tools menu. Be sure to Select All Interfaces.

        You also might have to change all occurence of
        "$PROGID_ExcelApplication8" to "$PROGID_ExcelApplicationXX"
        in the code, where XX is the Excel version you have.

        Thanks John and Peter, nice one...
        Pierre Bellisle

        [This message has been edited by Pierre Bellisle (edited February 27, 2004).]


        • #5
          The PowerBASIC documentation discusses the use of versionless ProgID strings, so at best only three minor changes to the INC file may be necessary:
          $PROGID_ExcelApplication8 = "Excel.Application.8"
          $PROGID_ExcelChart8       = "Excel.Chart.8"
          $PROGID_ExcelSheet8       = "Excel.Sheet.8"
          $PROGID_ExcelApplication  = "Excel.Application"
          $PROGID_ExcelChart        = "Excel.Chart"
          $PROGID_ExcelSheet        = "Excel.Sheet"

          PowerBASIC Support
          mailto:[email protected][email protected]</A>
          mailto:[email protected]