Sign in to follow this  
Followers 0
Jaboowaki

_ExcelWriteCell

16 posts in this topic

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


Share this post


Link to post
Share on other sites



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"

1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

Share this post


Link to post
Share on other sites

#3 ·  Posted (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 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

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


Share this post


Link to post
Share on other sites

@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

1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

Share this post


Link to post
Share on other sites

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

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, ???


1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

Share this post


Link to post
Share on other sites

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

More like this: ...

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

1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

Share this post


Link to post
Share on other sites

Perhaps this would help:

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

Share this post


Link to post
Share on other sites

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

1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

Share this post


Link to post
Share on other sites

Perhaps this would help:

$iNextRow = $oExcel.ActiveSheet.UsedRange.Rows.Count + 1
Good 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

Thanks for the lesson. ;)


Share this post


Link to post
Share on other sites

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

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

Change it to

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

1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

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