Excel Com Numeric Column References using Resize

[John Hackl]

'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

[Peter P Stephensen]

Hi John

Nice trick! Here is a version with reusable macros:

#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).]

[John Hackl]

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

[Pierre Bellisle]

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).]

[Lance Edmonds]

The PowerBASIC documentation discusses the use of versionless ProgID strings, so at best only three minor changes to the INC file may be necessary:

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>