Data to Excel challenge...

[USSSR]

Hello

I've been trying to find an answer for my "data to excel" challenge.. but no luck.  In a nutshell I wan't to collect data from a program and export it to Excel. 

This is my first time working with AUTOIT and excel so all your help is appreciated.

Basically I have 7 parameters:

$warranty_start
$km
$d_code
$c_number
$r_date
$VIN
$text

This is where help is needed as then I would need to open an existing excel sheet, check the next empty cell in row "A" and start copying these parameters to that line in specific columns.

Example:

Autoit checks next empty cell in column "A"

Declare this row as "next free row in column A"

Input $c_number to this row and column A

Next

Input $VIN to same row but in column B

Next

Input $d_code to same row but in column C

Next

Input $warranty start to same row but in column D

Next

Input $r_date start to same row but in column E

Next

Input $km to same row but in column F

Next and last

Input $text to same row but in column G

End func

 

This is far I have got but I just don´t know how to make it happen in excel... 

 

expandcollapse popup

#include <GuiConstants.au3>;Functions needed for most GUI Code
#include <Constants.au3>
#include "_ImageSearch_UDF.au3"
#include "_ImageSearch_Tool.au3"
#include <Excel.au3>


HotKeySet("{Esc}", "_Exit")
Func _Exit()
    Exit 0
 EndFunc   ;==>_Exit

;obtain data and export to excel


Local $hGUI = GuiCreate("Copy data to Excel", 430, 400);Create GUI Window
Local $idPic = GUICtrlCreatePic("logo.bmp", 10, 15, 200, 50) ; logokuva
$Button_1 = GuiCtrlCreateButton("Exit", 280, 20, 130, 50);Button_1 is the Exit Button
$Button_2 = GuiCtrlCreateButton("Copy data to Excel", 10, 140, 300, 50); Copy data from claim to -> excel
GuiSetState()

While 1;your LOOP
    $msg = GuiGetMsg();retrieves the button or control that was pressed
    Select; starts a case statement
    Case $msg = $GUI_EVENT_CLOSE;case statement, its like an if statement but better when you have alot of statements
    ExitLoop; If the program is exited it will exit the loop.
Case $msg = $Button_1
    ExitLoop;Exits the Loop
Case $msg = $Button_2
    Collect(); Copy data from claim to -> excel


 EndSelect; ends the case statements
WEnd


Func Collect();Collect data
Opt("WinTitleMatchMode", 2)
Local $hWnd = WinGetHandle("- Vaurion tiedot -")
ConsoleWrite ($hWnd & @CRLF)


WinActivate($hWnd)
WinWaitActive($hWnd)

;copy warranty start.
                     ClipPut("")
                     MouseClick("left", 340, 104, 2)
                     MouseClick("right",340, 104, 1)
                     Sleep(200)
                     Send("{DOWN 3}")
                     Sleep(200)
                     Send("{ENTER}")
                     Sleep(200)
                     $warranty_start = ClipGet()
                     ConsoleWrite("Warranty start = " & $warranty_start & @CRLF)
                     ClipPut("")

;copy km.
                     ClipPut("")
                     MouseClick("left", 402, 130, 2)
                     MouseClick("right",402, 130, 1)
                     Sleep(200)
                     Send("{DOWN 3}")
                     Sleep(200)
                     Send("{ENTER}")
                     Sleep(200)
                     $km = ClipGet()
                     ConsoleWrite("Km = " & $km & @CRLF)
                     ClipPut("")

;copy d_code.
                     ClipPut("")
                     MouseClick("left", 387, 203, 2)
                     MouseClick("right",387, 203, 1)
                     Sleep(200)
                     Send("{DOWN 3}")
                     Sleep(200)
                     Send("{ENTER}")
                     Sleep(200)
                     $d_code = ClipGet()
                     ConsoleWrite("D_code = " & $d_code & @CRLF)
                     ClipPut("")

;copy c_number
                     ClipPut("")
                     MouseClick("left",722, 82, 2)
                     MouseClick("right",722, 82, 1)
                     Sleep(200)
                     Send("{DOWN 3}")
                     Sleep(200)
                     Send("{ENTER}")
                     Sleep(200)
                     $c_number = ClipGet()
                     ConsoleWrite("$C_number = " & $c_number & @CRLF)
                     ClipPut("")

;copy r_date
                     ClipPut("")
                     MouseClick("left",724, 105, 2)
                     MouseClick("right",724, 105, 1)
                     Sleep(200)
                     Send("{DOWN 3}")
                     Sleep(200)
                     Send("{ENTER}")
                     Sleep(200)
                     $r_date = ClipGet()
                     ConsoleWrite("R_date = " & $r_date & @CRLF)
                     ClipPut("")

;copy VIN
                     ClipPut("")
                     MouseClick("left",1050, 105, 2)
                     MouseClick("right",1050, 105, 1)
                     Sleep(200)
                     Send("{DOWN 3}")
                     Sleep(200)
                     Send("{ENTER}")
                     Sleep(200)
                     $VIN = ClipGet()
                     ConsoleWrite("VIN = " & $VIN & @CRLF)
                     ClipPut("")

;Copy text field

    Local $_Image_syy = @ScriptDir & "\syy.bmp"

    Local $find_ListImage = $_Image_syy

      Local $return_syy = _ImageSearch($_Image_syy, 140, False)
      If $return_syy[0] = 1 Then
      ConsoleWrite("Checking text field"&@CRLF)
      MouseClick("left",$return_syy[1]+2, $return_syy[2]+53)
      Sleep(100)
      MouseMove($return_syy[1], $return_syy[2])
      Send ("{SHIFTDOWN}")
      Sleep(100)
      $rows_down = 200
      $rows_right = 100
      Send("{DOWN " & $rows_dow & "}")
      Sleep(100)
      Send("{RIGHT " & $rows_right & "}")
      Sleep(100)
      Send ("{SHIFTUP}")
      Sleep(300)
      Send("^c")
      Sleep(100)

$text = ClipGet()

ConsoleWrite("Text copying done" & @CRLF)
ClipPut("")
EndIf

ConsoleWrite("Opening excel" & @CRLF)
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, "\copydata.xlsx")


$newRow = $oExcel.ActiveCell.Row + 1
$oExcel.Range("A" & $newRow).Activate

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $c_number, $newRow)

If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite ", "Array successfully written.")

EndFunc

 

[Nine]

Have you tried to use ControlGetText ?  Instead of MouseClick / Send / Sleep, you could get the data with a single statement.  Use Au3Info.exe tool to get control information from your source application.

 

Edited September 21, 2021 by Nine

[USSSR]

1 minute ago, Nine said:

1- Have you tried to use ControlGetText ?  Instead of MouseClick / Send / Sleep, you could get the data with a single statement.  Use Au3Info.exe tool to get control information from your source application.

2- Use Excel UDF to write the data into the sheet.  (see _Excel_RangeRead AND _Excel_RangeWrite examples to learn how to open an existing book and write to it)

 

Thanks for the first tip. I will try to simplify the script as soon as I get this working.

For the second tip, I have succesfully opened existing book and also written to it.. But the problem for me is declaring the first free row and starting to write to it... This is where I need help. Believe me I have been googling and searching forum but now luck!

[Nine]

Local $NextRow = $oWorkbook.ActiveSheet.Usedrange.Rows.Count + 1

That will provide you the next free row.

[Nine]

Then use :

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $c_number, "A" & $NextRow)
If @error Then MsgBox(0, "Error", "Writing c_number")

Adding error handling is very much recommended

[USSSR]

6 minutes ago, Nine said:

Then use :

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $c_number, "A" & $NextRow)
If @error Then MsgBox(0, "Error", "Writing c_number")

Adding error handling is very much recommended

Thanks I got it working, you provide very good info. I just wonder that for the next six data values to be written, what is the best way to ensure that they are written to the same row than the first one? Do you have any advice?

[Nine]

Just repeat with the next value :

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $vin, "B" & $NextRow)
If @error Then MsgBox(0, "Error", "Writing vin")

Continue with the third, etc.

You could also stock the data into an array instead of individual variables, and write the whole array at once starting at column A

[USSSR]

12 minutes ago, Nine said:

Just repeat with the next value :

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $vin, "B" & $NextRow)
If @error Then MsgBox(0, "Error", "Writing vin")

Continue with the third, etc.

You could also stock the data into an array instead of individual variables, and write the whole array at once starting at column A

Thanks, I'm not yet familiar with variables. I will have to get in to checking what it is all about.