Jaboowaki Posted September 17, 2010 Share Posted September 17, 2010 How do I have _ExcelWriteCell find the next available row to start entering? Link to comment Share on other sites More sharing options...
seandisanti Posted September 17, 2010 Share 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" Link to comment Share on other sites More sharing options...
PsaltyDS Posted September 17, 2010 Share 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 Link to comment Share on other sites More sharing options...
Jaboowaki Posted September 17, 2010 Author Share Posted September 17, 2010 Am I executing the solution incorrectly. I get syntax error. lines 254 - 259 Link to comment Share on other sites More sharing options...
PsaltyDS Posted September 17, 2010 Share 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 Link to comment Share on other sites More sharing options...
seandisanti Posted September 17, 2010 Share 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 Link to comment Share on other sites More sharing options...
Jaboowaki Posted September 17, 2010 Author Share 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) Link to comment Share on other sites More sharing options...
seandisanti Posted September 17, 2010 Share 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, ??? Link to comment Share on other sites More sharing options...
PsaltyDS Posted September 17, 2010 Share 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 Link to comment Share on other sites More sharing options...
seandisanti Posted September 17, 2010 Share Posted September 17, 2010 More like this: ...very nice, sorry i still do my excel stuff the long way Link to comment Share on other sites More sharing options...
GMK Posted September 17, 2010 Share Posted September 17, 2010 Perhaps this would help:$iNextRow = $oExcel.ActiveSheet.UsedRange.Rows.Count + 1 Link to comment Share on other sites More sharing options...
seandisanti Posted September 17, 2010 Share 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 Link to comment Share on other sites More sharing options...
PsaltyDS Posted September 17, 2010 Share 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 Link to comment Share on other sites More sharing options...
Jaboowaki Posted September 19, 2010 Author Share Posted September 19, 2010 Thanks for the lesson. Link to comment Share on other sites More sharing options...
Jaboowaki Posted September 19, 2010 Author Share 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 Link to comment Share on other sites More sharing options...
seandisanti Posted September 19, 2010 Share Posted September 19, 2010 it's not $oExcel.Activesheet, Activesheet is an object under a Workbook... Change it to $oWB = $oExcel.ActiveWorkbook $oWB.Activesheet... Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now