issue with entering data into excel.

[DeadAdm1n]

hello to all that read this.

Over the last few weeks I have been working on a project to automate my job a little bit.  What I am trying to accomplish is to, at 15 mins till the hour, it will connect to a vnc server, maneuver around the remove server, and take screen shots of the information that I need, then enter it into an excel spreadsheet.

I have managed to get everything else running, almost perfect, the issue is that i scan 120 photos a night, and run the script to write the data to excel is that it keeps over writing the first row and not going to the next line to enter the next set of photos (one set of photos is 10 pictures)

I'm not sure 100% of what information is relevant, so Ill give you everything, that i feel necessary.

The Code:

expandcollapse popup

#include <Tesseract.au3>
#include <Array.au3>
#include <Excel.au3>

HotKeySet("e", "EndProgram")
Func EndProgram()
    Exit
EndFunc   ;==>EndProgram

$size = DirGetSize(@ScriptDir&"\Photos", 1)
dim $count = $size[1]
;MsgBox(0, "count", $count)

WinClose("[CLASS:Photo_Lightweight_Viewer]", "")
WinClose("[CLASS:XLMAIN]", "")
sleep(50)
ShellExecute("getFile.bat")
sleep(200)

WinWait("[CLASS:Photo_Lightweight_Viewer]", "")
WinActivate("[CLASS:Photo_Lightweight_Viewer]", '')
Sleep(200)
WinMove("[CLASS:Photo_Lightweight_Viewer]", '', '1', '1', 866, 735)
$_WinPos = WinGetPos("[CLASS:Photo_Lightweight_Viewer]", '')

Sleep(2000)
Local $oExcel = _Excel_Open()
Local $sWorkbook = @ScriptDir&"\Extras\GG.xlsx"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)


WinWait("[CLASS:XLMAIN]")
WinSetState("[CLASS:XLMAIN]", "", @SW_MAXIMIZE)
sleep(500)

WinActivate("[CLASS:Photo_Lightweight_Viewer]", '')
sleep(400)


Local $i = 0
Do
;-----------------------------ROLF--7---------------------------------------------------------
    $tube1 = _TesseractScreenCapture(0, "", 0, 4, 428, 416, 528, 440, 0)
    $case1 = _TesseractScreenCapture(0, "", 0, 4, 428, 480, 528, 506, 0)
    $water1 = _TesseractScreenCapture(0, "", 0, 4, 346, 320, 381, 333, 0)
    $oil11 = _TesseractScreenCapture(0, "", 0, 4, 497, 320, 529, 333, 0)
    $oil21 = _TesseractScreenCapture(0, "", 0, 4, 628, 320, 660, 333, 0)
    $oil31 = _TesseractScreenCapture(0, "", 0, 4, 750, 320, 796, 333, 0)
    nextPic()
    Sleep(400)
    $trans1 = _TesseractScreenCapture(0, "", 0, 4, 463, 340, 544, 360, 0)
    nextPic()
    Sleep(400)
    $1 = 2 + 1
    $2 = 2 + 1
    $3 = 2 + 1
    $4 = 2 + 1
    $5 = 2 + 1
    $6 = 2 + 1
    $7 = 2 + 1
    sleep(100)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $tube1, "b"&$1)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $case1, "c"&$2)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $water1, "e"&$3)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $oil11, "g"&$4)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $oil21, "h"&$5)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $oil31, "i"&$6)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $trans1, "l"&$7)
    sleep(2000)

    ;-----------------------------ROLF--8---------------------------------------------------------
    $tube2 = _TesseractScreenCapture(0, "", 0, 4, 428, 416, 528, 440, 0)
    $case2 = _TesseractScreenCapture(0, "", 0, 4, 428, 480, 528, 506, 0)
    $water2 = _TesseractScreenCapture(0, "", 0, 4, 346, 320, 381, 333, 0)
    $oil12 = _TesseractScreenCapture(0, "", 0, 4, 497, 320, 529, 333, 0)
    $oil22 = _TesseractScreenCapture(0, "", 0, 4, 628, 320, 660, 333, 0)
    $oil32 = _TesseractScreenCapture(0, "", 0, 4, 750, 320, 796, 333, 0)
    nextPic()
    Sleep(400)
    $trans2 = _TesseractScreenCapture(0, "", 0, 4, 463, 340, 544, 360, 0)
    nextPic()
    Sleep(400)
    $11 = 30 + 1
    $21 = 30 + 1
    $31 = 30 + 1
    $41 = 30 + 1
    $51 = 30 + 1
    $61 = 30 + 1
    $71 = 30 + 1
    sleep(100)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $tube2, "b"&$11)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $case2, "c"&$21)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $water2, "e"&$31)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $oil12, "g"&$41)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $oil22, "h"&$51)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $oil32, "i"&$61)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $trans2, "l"&$71)
    sleep(2000)

;-----------------------------ROLF--9---------------------------------------------------------
    $tube3 = _TesseractScreenCapture(0, "", 0, 4, 428, 416, 528, 440, 0)
    $case3 = _TesseractScreenCapture(0, "", 0, 4, 428, 480, 528, 506, 0)
    $water3 = _TesseractScreenCapture(0, "", 0, 4, 346, 320, 381, 333, 0)
    $oil13 = _TesseractScreenCapture(0, "", 0, 4, 497, 320, 529, 333, 0)
    $oil23 = _TesseractScreenCapture(0, "", 0, 4, 628, 320, 660, 333, 0)
    $oil33 = _TesseractScreenCapture(0, "", 0, 4, 750, 320, 796, 333, 0)
    nextPic()
    Sleep(400)
    $trans3 = _TesseractScreenCapture(0, "", 0, 4, 463, 340, 544, 360, 0)
    nextPic()
    Sleep(400)
    $12 = 58 + 1
    $22 = 58 + 1
    $32 = 58 + 1
    $42 = 58 + 1
    $52 = 58 + 1
    $62 = 58 + 1
    $72 = 58 + 1
    sleep(100)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $tube3, "b"&$12)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $case3, "c"&$22)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $water3, "e"&$32)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $oil13, "g"&$42)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $oil23, "h"&$52)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $oil33, "i"&$62)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $trans3, "l"&$72)
    sleep(2000)

;-----------------------------ROLF--10---------------------------------------------------------
    $tube4 = _TesseractScreenCapture(0, "", 0, 4, 428, 416, 528, 440, 0)
    $case4 = _TesseractScreenCapture(0, "", 0, 4, 428, 480, 528, 506, 0)
    $water4 = _TesseractScreenCapture(0, "", 0, 4, 346, 320, 381, 333, 0)
    $oil14 = _TesseractScreenCapture(0, "", 0, 4, 497, 320, 529, 333, 0)
    $oil24 = _TesseractScreenCapture(0, "", 0, 4, 628, 320, 660, 333, 0)
    $oil34 = _TesseractScreenCapture(0, "", 0, 4, 750, 320, 796, 333, 0)
    nextPic()
    Sleep(400)
    $trans4 = _TesseractScreenCapture(0, "", 0, 4, 463, 340, 544, 360, 0)
    nextPic()
    Sleep(400)
    $13 = 86 + 1
    $23 = 86 + 1
    $33 = 86 + 1
    $43 = 86 + 1
    $53 = 86 + 1
    $63 = 86 + 1
    $73 = 86 + 1
    sleep(100)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $tube4, "b"&$13)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $case4, "c"&$23)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $water4, "e"&$33)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $oil14, "g"&$43)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $oil24, "h"&$53)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $oil34, "i"&$63)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $trans4, "l"&$73)
    sleep(2000)

;----------------------------SPRING--11-------------------------------------------------------
    $tube5 = _TesseractScreenCapture(0, "", 0, 4, 428, 416, 528, 440, 0)
    $case5 = _TesseractScreenCapture(0, "", 0, 4, 428, 480, 528, 506, 0)
    $water5 =  _TesseractScreenCapture(0, "", 0, 4, 346, 320, 381, 333, 0)
    $oil15 = _TesseractScreenCapture(0, "", 0, 4, 497, 320, 529, 333, 0)
    $oil25 = _TesseractScreenCapture(0, "", 0, 4, 628, 320, 660, 333, 0)
    $oil35 = _TesseractScreenCapture(0, "", 0, 4, 750, 320, 796, 333, 0)
    nextPic()
    Sleep(400)
    $trans5 = _TesseractScreenCapture(0, "", 0, 4, 463, 340, 544, 360, 0)
    nextPic()
    Sleep(400)
    $14 = 114 + 1
    $24 = 114 + 1
    $34 = 114 + 1
    $44 = 114 + 1
    $54 = 114 + 1
    $64 = 114 + 1
    $74 = 114 + 1
    sleep(100)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $tube5, "b"&$14)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $case5, "c"&$24)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $water5, "e"&$34)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $oil15, "g"&$44)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $oil25, "h"&$54)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $oil35, "i"&$64)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $trans5, "l"&$74)
    sleep(500)
    $i = $i +10

Until $i = $count








;----------------------------------Functions------------------------------------------------------------


Func nextPic()
    Sleep(500)
    send("{RIGHT}")
    Sleep(500)
EndFunc   ;==>nextPic

 

tesseract.au3 that my script makes a call to, I do not see its relevence. 

Tesseract.au3

I am using 5.1.0 tasseract from google:

https://github.com/tesseract-ocr/tesseract

The batch file code that my script executes:

FOR %%F IN (%cd%\Photos\*.png) DO (
 set filename=%%F
 goto tests
)
:tests
start %systemroot%\System32\rundll32.exe "%ProgramFiles%\Windows Photo Viewer\PhotoViewer.dll", ImageView_Fullscreen %filename%

and the spreadsheet is from Microsoft office 2016.

The pictures that tesseract scans.

Spoiler

thank you for your time.

Edited June 12, 2022 by DeadAdm1n
added pictures to post

[water]

I have never worked with Tesseract. What does function _TesseractScreenCapture return?
A picture? Then write the picture to a file and use _ExceL_PictureAdd to place the picture in Excel.

[DeadAdm1n]

3 hours ago, water said:

I have never worked with Tesseract. What does function _TesseractScreenCapture return?
A picture? Then write the picture to a file and use _ExceL_PictureAdd to place the picture in Excel.

Tesseract is an OCR udf, and _tesseractscreencapture returns a a number

in my case it returns 5 numbers before it writes to excel, which the first set of pictures work, and if I just copy paste the code and change the vrange, it will enter 2 sets of pictures fine, when I put the code in a Do and change the vrange in rangewrite, it just keeps over writing the first set of data from the pictures

Edited June 12, 2022 by DeadAdm1n

[DeadAdm1n]

i removed a lot of the code, to eliminate a little bit of the confusion,  to help focus on my main issue, and added marks to the code.

when it goes to the next row, it overwrites the previous.

 

expandcollapse popup

#include <Array.au3>
#include <Excel.au3>


WinClose("[CLASS:XLMAIN]", "")

Sleep(2000)
Local $oExcel = _Excel_Open()
Local $sWorkbook = @ScriptDir&"\Extras\GG.xlsx"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)

$count = 30

Local $i = 0
Do
;--------------------------Well--Name----------Rofl-7-----------------------

; random info
    $tube1 = 1233
    $case1 = 233
    $water1 = 5.67
    $oil11 = 4.32
    $oil21 = 5.23
    $oil31 = 634
    $trans1 =14.5


; an attempt to write to the same column but the next row without 1 million lines of code.
    Sleep(400)
    $1 = 2 +1
    $2 = 2 +1
    $3 = 2 +1
    $4 = 2 +1
    $5 = 2 +1
    $6 = 2 +1
    $7 = 2 +1

    ;write to excel, the $vValue is pulled from tube1,case1,water1 ect. the vrange is the first letter of the column,
    ; then trying to make it count for the number in the Do function.
    sleep(100)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $tube1, "b"&$1)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $case1, "c"&$2)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $water1, "e"&$3)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $oil11, "g"&$4)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $oil21, "h"&$5)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $oil31, "i"&$6)
    sleep(200)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $trans1, "l"&$7)
    sleep(1000)

    $i = $i +10

Until $i = $count


;MsgBox($MB_SYSTEMMODAL, "vRange test", "b"&$7) ;writes b3
;ConsoleWrite("b"&$7) ;writes b3

MsgBox($MB_SYSTEMMODAL, "getPictures", "Finished.", 4)

 

Edited June 13, 2022 by DeadAdm1n
typos

[DeadAdm1n]

figured it out.

I needed to put the $1 threw $7 outside the Do then make all those $1 = $1 +1 at the end.