Jaboowaki 0 Posted September 17, 2010 How do I have _ExcelWriteCell find the next available row to start entering? Share this post Link to post Share on other sites
seandisanti 6 Posted September 17, 2010 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" Share this post Link to post Share on other sites
PsaltyDS 39 Posted September 17, 2010 (edited) @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 Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Share this post Link to post Share on other sites
Jaboowaki 0 Posted September 17, 2010 Am I executing the solution incorrectly. I get syntax error. lines 254 - 259 Share this post Link to post Share on other sites
PsaltyDS 39 Posted September 17, 2010 Post lines 254 thru 259... Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Share this post Link to post Share on other sites
seandisanti 6 Posted September 17, 2010 @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 Share this post Link to post Share on other sites
Jaboowaki 0 Posted September 17, 2010 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) Share this post Link to post Share on other sites
seandisanti 6 Posted September 17, 2010 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, ??? Share this post Link to post Share on other sites
PsaltyDS 39 Posted September 17, 2010 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) Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Share this post Link to post Share on other sites
seandisanti 6 Posted September 17, 2010 More like this: ...very nice, sorry i still do my excel stuff the long way Share this post Link to post Share on other sites
GMK 25 Posted September 17, 2010 Perhaps this would help:$iNextRow = $oExcel.ActiveSheet.UsedRange.Rows.Count + 1 Share this post Link to post Share on other sites
seandisanti 6 Posted September 17, 2010 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 Share this post Link to post Share on other sites
PsaltyDS 39 Posted September 17, 2010 Perhaps this would help:$iNextRow = $oExcel.ActiveSheet.UsedRange.Rows.Count + 1Good call, more direct. Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Share this post Link to post Share on other sites
Jaboowaki 0 Posted September 19, 2010 Thanks for the lesson. Share this post Link to post Share on other sites
Jaboowaki 0 Posted September 19, 2010 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 Share this post Link to post Share on other sites
seandisanti 6 Posted September 19, 2010 it's not $oExcel.Activesheet, Activesheet is an object under a Workbook... Change it to $oWB = $oExcel.ActiveWorkbook $oWB.Activesheet... Share this post Link to post Share on other sites