Jump to content

Data to Excel challenge...


Recommended Posts

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... 

 

#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

 

Link to post
Share on other sites

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 by Nine
Link to post
Share on other sites
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!

Link to post
Share on other sites
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?

Link to post
Share on other sites

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

Link to post
Share on other sites
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.

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...