Selecting excel cell

[lorenkinzel]

I ran into a little puzzle;

Func selectCell()

$Row = GUICtrlRead($locateRowInput)

$Column = GUICtrlRead($locateColumnInput)

$oExcel.Range($oExcel.Cells($Row, $Column), $oExcel.Cells($Row, $Column)).Select

EndFunc

Does not work

Func selectCell()

$Row = GUICtrlRead($locateRowInput)

$Row +=1;not sure why I had to do this, but it otherwise does not work

$Row -=1

$Column = GUICtrlRead($locateColumnInput)

$Column +=1

$Column -=1

$oExcel.Range($oExcel.Cells($Row, $Column), $oExcel.Cells($Row, $Column)).Select

EndFunc

Does work

For lack of knowing better, I've called this sort of thing "a layer of seperation".

I would like to leave that "lack of knowing better" thing in the past.

Can anyone explain?

[BrewManNH]

Is $locateRowInput a global variable? Is there anything in the control that you're trying to read from?

Usually it's a lot better to post a working script when requesting help because right now if anyone wanted to help, they'd need to recreate the script you've already written, and 99% of the time they'd do it differently.

[lorenkinzel]

Sorry for being vague, it's an input.

expandcollapse popup

#include <ButtonConstants.au3>
#include <GUIConstantSex.au3>
#include <EditConstants.au3>
#include <WindowsConstants.au3>
#include <Excel.au3>
Opt("GUIOnEventMode", 1)

$testIP_GUI = GUICreate("barestBoneTester", 548, 442, 192, 124)
WinSetOnTop("barestBoneTester", "", 1)
GUISetOnEvent($GUI_EVENT_CLOSE, "xIt")
Func xIt()
Exit
EndFunc ;==>xIt

$oExcel = ObjGet("", "Excel.Application"); Get an EXISTING Excel Object (book open before script)
$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc") ;Initialize a COM error handler

$activeCellNumInput = GUICtrlCreateInput("activeCellINumInput", 16, 8, 105, 21)
$getAddressButton = GUICtrlCreateButton("get cell address", 144, 8, 105, 33)
GUICtrlSetOnEvent($getAddressButton, "GetAddress")

$activeRowNumInput = GUICtrlCreateInput("activeRow #", 16, 64, 105, 21)
$rowNumLabel = GUICtrlCreateLabel("active cell row #", 126, 64, 105, 21)

$activeColumnNumInput = GUICtrlCreateInput("activeColumn #", 16, 96, 105, 21)
$columnNumLabel = GUICtrlCreateLabel("active cell column #", 126, 96, 105, 21)

$row_colSplit = GUICtrlCreateButton("R1 / C1 split", 236, 64, 105, 33)
GUICtrlSetOnEvent($row_colSplit, "splitCellNum")

$cellContentsInput = GUICtrlCreateInput("cellContentsInput", 16, 136, 249, 21)
$getContentsButton = GUICtrlCreateButton("get cell contents", 144, 168, 105, 33)
GUICtrlSetOnEvent($getContentsButton, "readCell")

$bookNameInput = GUICtrlCreateInput("book name", 16, 232, 249, 21)
$bookNameLabel = GUICtrlCreateLabel("excel book name", 92, 254, 82, 15)

$stuffToWriteToCellInput = GUICtrlCreateInput("write THIS to selected cell", 16, 290, 249, 21)
$writeToCellButton = GUICtrlCreateButton("write to cell", 16, 315, 105, 33)
GUICtrlSetOnEvent($writeToCellButton, "write2Cell")

$restartButton = GUICtrlCreateButton("restart", 440, 16, 75, 21)
GUICtrlSetOnEvent($restartButton, "_restart")
GUICtrlSetBkColor($restartButton, 0x5f005f)
GUICtrlSetColor($restartButton, 0xffffff)

$rightArrow = GUICtrlCreateButton(">", 440, 240, 42, 42)
GUICtrlSetFont(-1, 24, 800)
GUICtrlSetOnEvent($rightArrow, "rightOneCell")

$upArrow = GUICtrlCreateButton("^", 398, 198, 42, 42)
GUICtrlSetFont($upArrow, 38, 440)
GUICtrlSetOnEvent($upArrow, "upOneCell")

$leftArrow = GUICtrlCreateButton("<", 356, 240, 42, 42)
GUICtrlSetFont($leftArrow, 24, 800)
GUICtrlSetOnEvent($leftArrow, "leftOneCell")

$downArrow = GUICtrlCreateButton("v", 398, 282, 42, 42)
GUICtrlSetFont($downArrow, 27, 440)
GUICtrlSetOnEvent($downArrow, "downOneCell")

$cellLabel = GUICtrlCreateLabel("cell", 410, 241, 18, 20)
$selectLabel = GUICtrlCreateLabel("select", 405, 261, 28, 20)

$locateRowInput = GUICtrlCreateInput("enter row #", 360, 358, 105, 21)
$locateColumnInput = GUICtrlCreateInput("enter column #", 360, 380, 105, 21)
$selectLocatedCellButton = GUICtrlCreateButton("select that cell", 360, 402, 105, 21)
GUICtrlSetOnEvent($selectLocatedCellButton, "selectCell")

$wintitle = WinGetTitle("Microsoft Excel")
GUICtrlSetData($bookNameInput, $wintitle)
GUISetState(@SW_SHOW)

While 1
Sleep(20)
WEnd

Func MyErrFunc();by others
Local $err = $oMyError.number
If $err = 0 Then $err = -1
SetError($err)
EndFunc ;==>MyErrFunc

Func GetAddress()
Local $Address
While WinExists($wintitle)
Sleep(50)
$actual = $oExcel.ActiveCell.Address
If Not @error Then
If $Address <> $actual Then
$Address = $actual
GUICtrlSetData($activeCellNumInput, $Address)
EndIf
EndIf
Sleep(100)
ExitLoop
WEnd
EndFunc ;==>GetAddress

Func splitCellNum()
While WinExists($wintitle)
$Row = $oExcel.ActiveCell.Row
$Column = $oExcel.ActiveCell.Column
GUICtrlSetData($activeRowNumInput, $Row)
GUICtrlSetData($activeColumnNumInput, $Column)
Sleep(100)
ExitLoop
WEnd
EndFunc ;==>splitCellNum

Func readCell()
Local $Address
Local $actual = $oExcel.ActiveCell.Address
If Not @error Then
If $Address <> $actual Then
$Address = $actual
GUICtrlSetData($activeCellNumInput, $Address)
EndIf
EndIf
Local $cellNum = GUICtrlRead($activeCellNumInput)
Sleep(200)
Local $cellInfo = _ExcelReadCell($oExcel, $cellNum)
GUICtrlSetData($cellContentsInput, $cellInfo)
EndFunc ;==>readCell

Func write2Cell()
Local $readTheInput = GUICtrlRead($stuffToWriteToCellInput)
Local $cellNum = GUICtrlRead($activeCellNumInput)
_ExcelWriteCell($oExcel, $readTheInput, $cellNum);Probably should change to R1C1
;Sleep(200)
;Local $handle = WinGetHandle($wintitle)
;ControlSend($handle, "", "", "{ENTER}")
;Sleep(200)
;WinGetHandle($wintitle)
EndFunc ;==>write2Cell

Func rightOneCell()
;splitCellNum()
;Sleep(100)
;Local $Row = GUICtrlRead($activeRowNumInput)
;Local $Column = GUICtrlRead($activeColumnNumInput)
;$Column += 1
;using the above, left & right work but up & down do not.(???)
;using the below, all 4 work.
$Row = $oExcel.ActiveCell.Row
$Column = $oExcel.ActiveCell.Column
$Column += 1
;MsgBox(0, "", $column)
$oExcel.Range($oExcel.Cells($Row, $Column), $oExcel.Cells($Row, $Column)).Select;JoHanatCent 09 December 2010
splitCellNum()
Sleep(50)
GetAddress()
EndFunc ;==>rightOneCell

Func downOneCell()
$Row = $oExcel.ActiveCell.Row
$Column = $oExcel.ActiveCell.Column
$Row += 1
$oExcel.Range($oExcel.Cells($Row, $Column), $oExcel.Cells($Row, $Column)).Select
splitCellNum()
Sleep(50)
GetAddress()
EndFunc ;==>downOneCell

Func leftOneCell()
$Row = $oExcel.ActiveCell.Row
$Column = $oExcel.ActiveCell.Column
$Column -= 1
$oExcel.Range($oExcel.Cells($Row, $Column), $oExcel.Cells($Row, $Column)).Select
splitCellNum()
Sleep(50)
GetAddress()
EndFunc ;==>leftOneCell

Func upOneCell()
$Row = $oExcel.ActiveCell.Row
$Column = $oExcel.ActiveCell.Column
$Row -= 1
$oExcel.Range($oExcel.Cells($Row, $Column), $oExcel.Cells($Row, $Column)).Select
splitCellNum()
Sleep(50)
GetAddress()
EndFunc ;==>upOneCell

Func selectCell()
$Row = GUICtrlRead($locateRowInput)
$Row +=1;not sure why I had to do this, but it otherwise does not work
$Row -=1
$Column = GUICtrlRead($locateColumnInput)
$Column +=1
$Column -=1
$oExcel.Range($oExcel.Cells($Row, $Column), $oExcel.Cells($Row, $Column)).Select
EndFunc

Func _restart();Azjio (I think)
Local $sAutoIt_File = @TempDir & "\~Au3_ScriptRestart_TempFile.au3"
Local $sRunLine, $sScript_Content, $hFile
$sRunLine = @ScriptFullPath
If Not @Compiled Then $sRunLine = @AutoItExe & ' /AutoIt3ExecuteScript ""' & $sRunLine & '""'
If $CmdLine[0] > 0 Then $sRunLine &= ' ' & $CmdLineRaw
$sScript_Content &= '#NoTrayIcon' & @CRLF & _
'While ProcessExists(' & @AutoItPID & ')' & @CRLF & _
' Sleep(10)' & @CRLF & _
'WEnd' & @CRLF & _
'Run("' & $sRunLine & '")' & @CRLF & _
'FileDelete(@ScriptFullPath)' & @CRLF
$hFile = FileOpen($sAutoIt_File, 2)
FileWrite($hFile, $sScript_Content)
FileClose($hFile)
Run(@AutoItExe & ' /AutoIt3ExecuteScript "' & $sAutoIt_File & '"', @ScriptDir, @SW_HIDE)
Sleep(1000)
Exit
EndFunc ;==>_restart


;Cells.Find(What:="2x4", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
; xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
; , SearchFormat:=False).Activate

edit: misleading text in column select input

edit2: fixed lysdexic typo in previous edit

Edited February 11, 2013 by lorenkinzel

[BrewManNH]

Try this, without the +/- after it.

$Row = Number(GUICtrlRead($locateRowInput))

Edited February 11, 2013 by BrewManNH

[lorenkinzel]

So the +/- is getting me a numeric value as opposed to other than numeric?

[BrewManNH]

GUICtrlRead returns a string, perhaps the Excel API requires it to be a number.

[lorenkinzel]

(Lightbulb appears over head)

Aha! Thanks. It worked fine as was, but looked a little Rube-Goldberg.