Jump to content

issue with entering data into excel.


Recommended Posts

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:

#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

000917.png.b576e75326868443be31e775676c894f.png000916.png.6c0ce9f8e960b621f71f37bd9506bc77.png

thank you for your time.

Edited by DeadAdm1n
added pictures to post
Link to post
Share on other sites

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
Posted (edited)
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 by DeadAdm1n
Link to post
Share on other sites
Posted (edited)

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.

 

#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 by DeadAdm1n
typos
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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...