_ExcelWriteCell

[Jaboowaki]

How do I have _ExcelWriteCell find the next available row to start entering?

[seandisanti]

How do I have _ExcelWriteCell find the next available row to start entering?

i don't know about that, but you could just do...

$i = 1
While $oEX.Activesheet.Range("a" & $i).formula <> ""
$i ++
Wend
$oEX.Activesheet.Range("a" & $i).formula = "whatever"

[PsaltyDS]

@cameronsdad: That assumes the first column is used on every row - not reliable.

@Jaboowaki: Look inside Excel.au3 at how the _ExcelReadSheetToArray() function determines it:

; Get size of current sheet as R1C1 string
    ;     Note: $xlCellTypeLastCell and $x1R1C1 are constants declared in ExcelCOM_UDF.au3
    Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)

    ; Extract integer last row and col
    $sLastCell = StringRegExp($sLastCell, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
    Local $iLastRow = $sLastCell[0]
    Local $iLastColumn = $sLastCell[1]

    ; Return 0's if the sheet is blank
    If $sLastCell = "R1C1" And $oExcel.Activesheet.Cells($iLastRow, $iLastColumn).Value = "" Then Return $avRET

If you don't want to go through that for yourself, just do this:

#Include <Excel.au3>

; ...

$aRET = _ExcelReadSheetToArray($oExcel)
; The last row is in $aRET[0][0]
; The last col is in $aRET[0][1]

Edited September 17, 2010 by PsaltyDS

[Jaboowaki]

Am I executing the solution incorrectly. I get syntax error. lines 254 - 259

[PsaltyDS]

Post lines 254 thru 259...

[seandisanti]

@cameronsdad: That assumes the first column is used on every row - not reliable.

True, but i figured they could change it to a column that is used on every row

[Jaboowaki]

This is where I'm hung up.

Select

Case $msg = $GUI_EVENT_CLOSE

Exit

Case $msg = $ButtonCancel

Exit

Case $msg = $ButtonOK

$oExcel = _ExcelBookOpen($PCreport, 1)

$i = 1

While $oExcel.Activesheet.Range("a" & $i).formula <> ""

$i ++

WEnd

$oExcel.Activesheet.Range("a" & $i).formula = "whatever"

_ExcelWriteCell($oExcel, GUICtrlRead($InputPallet), $i, 1)

_ExcelWriteCell($oExcel, GUICtrlRead($InputPlace), $i, 2)

_ExcelWriteCell($oExcel, $ISPF, $i, 3)

_ExcelWriteCell($oExcel, "TESTED", $i, 4)

_ExcelWriteCell($oExcel, GUICtrlRead($ListCond), $i, 5)

_ExcelWriteCell($oExcel, GUICtrlRead($InputMfg), $i, 6)

_ExcelWriteCell($oExcel, GUICtrlRead($InputModel), $i, 7)

_ExcelWriteCell($oExcel, GUICtrlRead($ListFF), $i, 8)

_ExcelWriteCell($oExcel, GUICtrlRead($InputCpuQty), $i, 9)

_ExcelWriteCell($oExcel, GUICtrlRead($ListCpuMfg), $i, 10)

_ExcelWriteCell($oExcel, GUICtrlRead($InputCpuSpeed), $i, 11)

_ExcelWriteCell($oExcel, GUICtrlRead($InputCpuFsb), $i, 12)

_ExcelWriteCell($oExcel, GUICtrlRead($InputMemTotal), $i, 13)

_ExcelWriteCell($oExcel, GUICtrlRead($ListMemType), $i, 14)

_ExcelWriteCell($oExcel, GUICtrlRead($InputHddSize), $i, 15)

_ExcelWriteCell($oExcel, GUICtrlRead($ListHddSpeed), $i, 16)

_ExcelWriteCell($oExcel, GUICtrlRead($ListHddConn), $i, 17)

_ExcelWriteCell($oExcel, GUICtrlRead($ListOptiDrive1), $i, 18)

_ExcelWriteCell($oExcel, GUICtrlRead($ListOptiDrive2), $i, 19)

_ExcelWriteCell($oExcel, GUICtrlRead($InputVidMod), $i, 20)

_ExcelWriteCell($oExcel, GUICtrlRead($InputVidSize), $i, 21)

_ExcelWriteCell($oExcel, GUICtrlRead($ListCardInt), $i, 22)

_ExcelWriteCell($oExcel, GUICtrlRead($InputPciCards), $i, 23)

_ExcelWriteCell($oExcel, GUICtrlRead($ListPorts), $i, 24)

_ExcelWriteCell($oExcel, GUICtrlRead($ListWinCoa), $i, 25)

_ExcelWriteCell($oExcel, GUICtrlRead($EditNotes), $i, 26)

_ExcelWriteCell($oExcel, @UserName, $i, 27)

_ExcelBookSave($oExcel, 0)

_ExcelBookClose($oExcel, 0, 0)

[seandisanti]

This is where I'm hung up.

so the problem is somewhere in all of that; thanks for clarifying

is it not writing where it's supposed to, throwing an error, ???

[PsaltyDS]

More like this:

$oExcel = _ExcelBookOpen($PCreport, 1)
$aRET = _ExcelReadSheetToArray($oExcel)  ; The last row is in $aRET[0][0], last col is in $aRET[0][1]
$iNextRow = $aRET[0][0] + 1 ; Next row after last used

_ExcelWriteCell($oExcel, GUICtrlRead($InputPallet), $iNextRow, 1)
; ...
_ExcelWriteCell($oExcel, @UserName, $iNextRow, 27)

[seandisanti]

More like this: ...

very nice, sorry i still do my excel stuff the long way

[GMK]

Perhaps this would help:

$iNextRow = $oExcel.ActiveSheet.UsedRange.Rows.Count + 1

[seandisanti]

Perhaps this would help:

$iNextRow = $oExcel.ActiveSheet.UsedRange.Rows.Count + 1

THAT is the one that i was looking for, couldn't remember it to save my life and i was going through the object browser between calls

[PsaltyDS]

Perhaps this would help:

$iNextRow = $oExcel.ActiveSheet.UsedRange.Rows.Count + 1

Good call, more direct.

[Jaboowaki]

Thanks for the lesson.

[Jaboowaki]

Ok, this errors with (248) : ==> Variable must be of type "Object".:

$i = $oExcel.ActiveSheet.UsedRange.Rows.Count + 1

$i = $oExcel^ ERROR

Here's the section the errors in...

$begin=0

While 1

dim $msg = GUIGetMsg()

Select

Case $msg = $GUI_EVENT_CLOSE

Exit

Case $msg = $ButtonCancel

Exit

Case $msg = $ButtonOK

$oExcel = _ExcelBookOpen($PCreport, 1)

$i = $oExcel.ActiveSheet.UsedRange.Rows.Count + 1

_ExcelWriteCell($oExcel, GUICtrlRead($InputPallet), $i, 1)

_ExcelWriteCell($oExcel, GUICtrlRead($InputPlace), $i, 2)

_ExcelWriteCell($oExcel, $ISPF, $i, 3)

_ExcelWriteCell($oExcel, "TESTED", $i, 4)

_ExcelWriteCell($oExcel, GUICtrlRead($ListCond), $i, 5)

_ExcelWriteCell($oExcel, GUICtrlRead($InputMfg), $i, 6)

_ExcelWriteCell($oExcel, GUICtrlRead($InputModel), $i, 7)

_ExcelBookSave($oExcel, 0)

_ExcelBookClose($oExcel, 0, 0)

GUIDelete("FillForm")

sleep(30)

Call("FillForm")

sleep(30)

EndSelect

WEnd

[seandisanti]

it's not $oExcel.Activesheet, Activesheet is an object under a Workbook...

Change it to

$oWB = $oExcel.ActiveWorkbook
$oWB.Activesheet...