Jump to content
Sign in to follow this  
Dana

Excel: writing a row

Recommended Posts

Dana

I'm trying to append a row of data to an Excel file.  I have:

;values for debug
    $flowfinalflow = 999.99
    $flowfinalpress = 888.88
    $flowcode = "FF"
    $crackfinalpress = 777.77
    $crackfinalflow = 666.66
    $crackcode = "CC"
    $leakfinalflow = 555.55
    $leakfinalpress = 444.44
    $leakcode = "LL"
    $partnum = "TEST00123456S"
    ;end of debug values
    $time = _Now()

    If $flowcode = "AC" And $crackcode = "AC" And $leakcode = "AC" Then
        $passfail = "PASS"
    Else
        $passfail = "FAIL"
    EndIf

    $logstring = "FLOW:" & @CRLF & "Flow: " & $flowfinalflow & @CRLF & "Pressure: " & $flowfinalpress & @CRLF & "Result: " & $flowcode & @CRLF & @CRLF & _
            "CRACK:" & @CRLF & "Pressure: " & $crackfinalpress & @CRLF & "Flow: " & $crackfinalflow & @CRLF & "Result: " & $crackcode & @CRLF & @CRLF & _
            "LEAK:" & @CRLF & "Flow: " & $leakfinalflow & @CRLF & "Pressure: " & $leakfinalpress & @CRLF & "Result: " & $leakcode & @CRLF & @CRLF & _
            "TIME :" & $time
    SplashTextOn("Data Logging", $logstring, 250, 350, @DesktopWidth - 1030, 20, $DLG_TEXTLEFT)
    local $dataline[] = [$time, $partnum, $flowfinalflow, $flowfinalpress, $flowcode, $crackfinalpress, $crackfinalflow, $crackcode, $leakfinalflow, $leakfinalpress, $leakcode, $passfail]
    ;_ArrayDisplay($dataline)
    $oExcel = _Excel_Open(0, 0, 0, 0)
    $oWorkbook = _Excel_BookOpen($oExcel, "p:\alicat\A58_Datalog.xls")
    $nextrow = $oWorkbook.ActiveSheet.UsedRange.Rows.Count + 1
    _Excel_RangeWrite($oWorkbook, Default, $dataline, "A" & $nextrow)
    ConsoleWrite(@error)
    _Excel_BookSave($oWorkbook)
    _Excel_BookClose($oWorkbook)
    _Excel_Close($oExcel)
    ;Sleep(4000)
    SplashOff()

The problem is that the values get written down the A column; what I want is for the values to be written to columns A-L of the appropriate line.  I tried changing the write line to:

_Excel_RangeWrite($oWorkbook, Default, $dataline, "A" & $nextrow & ":L" & $nextrow)

But then I get the first value of the array ($time) written to each cell of the line.  What am I doing wrong?

Share this post


Link to post
Share on other sites
Dana

Figured it out, added _ArrayTranspose($dataline).  But is there any way to do it without doing that, i.e. creating the array already transposed?

Share this post


Link to post
Share on other sites
AutoBert

it must be a 2D-array:

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and create a new workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookNew($oExcel)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Write a part of a 2D array to the active sheet in the active workbook
; *****************************************************************************
Local $aArray2D[1][5] = [[11, 12, 13, 14, 15]]
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray2D, "B1")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 3", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 3", "2D array successfully written.")

from example 3 in help to _Excel_RangeWrite.

Share this post


Link to post
Share on other sites
Dana

Thanks, that did it.  But though it works, I've never seen an array declared this way before:

Local $aArray2D[1][5] = [[11, 12, 13, 14, 15]]

Where is that method of assigning the array documented?  I couldn't find it anywhere.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×