Announcement

Collapse

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

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel Com Numeric Column References using Resize

    Code:
    'Uses Excel "Resize" to avoid alpha column references when assigning an
    'array to a range
    #DIM ALL
    #COMPILE EXE "xlresize.exe"
    #INCLUDE "ExcelApp.inc"
    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
    
        IF ISFALSE ISOBJECT(oExcelApp) THEN
           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
    
    Terminate:
        SET oExcelApp       = NOTHING
        SET oExcelWorkbook  = NOTHING
        SET oExcelWorkSheet = NOTHING
        SET oFirstCellRange = NOTHING
        SET oOutRange       = NOTHING
        FUNCTION = 0
    END FUNCTION
    
    FUNCTION PBMAIN
       DIM lReturnValue AS LONG
       lReturnValue = PbcomXlResize(1000,5)
    END FUNCTION

  • #2
    Hi John

    Nice trick! Here is a version with reusable macros:
    Code:
    #dim all
    #compile exe "xlresize.exe"
    #include "Excel.inc"
     
    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)
     
    Terminate:
        set oExcelApp       = NOTHING
        set oExcelWorkbook  = NOTHING
        set oExcelWorkSheet = NOTHING
     
    end function
     
    function pbmain
       PbcomXlResize 1000,5
    end function
    ------------------
    [email protected] www.dreammodel.dk

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

    Comment


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

      Comment


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

        John once wrote...

        - Try: c:\PBCC30\samples\com\excel\excelapp.inc
        - 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).]

        Comment


        • #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:
          Code:
          Change:
          $PROGID_ExcelApplication8 = "Excel.Application.8"
          $PROGID_ExcelChart8       = "Excel.Chart.8"
          $PROGID_ExcelSheet8       = "Excel.Sheet.8"
           
          to:
           
          $PROGID_ExcelApplication  = "Excel.Application"
          $PROGID_ExcelChart        = "Excel.Chart"
          $PROGID_ExcelSheet        = "Excel.Sheet"

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

          Comment

          Working...
          X