Sign in to follow this  
Followers 0
Dana

Excel: writing a row

5 posts in this topic

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



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

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

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  
Followers 0