need help with speeding up the script

[dickjones007]

attached a video of script doing its thing

is there any other way of doing this to significantly increase speed

table has 20000+ rows which will roughly take ~18hrs to complete the process

code:

#include <excel.au3>

Local $sFilePath1 = @ScriptDir & "\Test.xlsx"
Local $oExcel = _ExcelBookOpen($sFilePath1,1)
WinWaitActive("Microsoft Excel - test")
WinActivate("Materijalno knjigovodstvo - [Odabir artikla 1]")
Send("^{HOME}") ;goes to first row of entire table
Send("{HOME}") ;goes to begining of row
$brojredova = StringRight(ControlGetText("Materijalno knjigovodstvo - [Odabir artikla 1]", "", 66446), 5) ;reads the number of rows
$hTimer = TimerInit()
For $i = 0 To 5 ;row by row (5 will be replaced with $brojredova)
    For $j = 0 To 4 ;column by column (without the last one)
        WinActivate("Materijalno knjigovodstvo - [Odabir artikla 1]")
        Send("^c") ;copies selected field value
        _ExcelWriteCell($oExcel, ClipGet(), $i + 1, $j + 1)
        Send("{RIGHT}") ;select next right vaule
    Next
            Send("{DOWN}") ;goes to next row
            Send("{HOME}") ;goes to beginning of row
Next
$iDiff = TimerDiff($hTimer)
MsgBox(0, "Time Difference", $iDiff/1000)
;~ WinActivate("Microsoft Excel - test")
;~ Sleep(100)
_ExcelBookSave($oExcel)
_ExcelBookClose($oExcel)

cannot select entire row

taking questions and suggestions.

C41686700260.rar

[BrewManNH]

What are you trying to accomplish?

[JohnOne]

Something wrong with the video hosting websites?

[dickjones007]

@BrewManNH

im trying to transfer entire table to excel so i can work with it

@JohnOne

what do you mean?

 

i have also been trying with control get text but no result

Edited February 16, 2014 by dickjones007

[JohnOne]

What is the app you're automating?

[dickjones007]

i am trying to make automatic order filling app by just scanning products barcode

only thing i have trouble with is active state of warehouse items

which i am trying to resolve by exporting table from my company's software to excel

[JohnOne]

Your companies software must load its data from a database file.

The only way you're going to speed it up is to access that database directly.

[dickjones007]

in the upper right part of video there is text in the "table app" saying "Zapis #/22"

i can read that text with :

ControlGetText("Materijalno knjigovodstvo - [Odabir artikla 1]", "", 66446)

with 66446 being the ID of that control

but when trying ID 66430 (ID of main table) i get nothing in the msgbox

[dickjones007]

yes it loads that table from mysql database to which i have no access nor will i get it

is there maybe any friendly way to access it that i dont know of?

Edited February 16, 2014 by dickjones007

[Werty]

Something wrong with the video hosting websites?

 

Yes they all use flash or/and require "modern" browsers. I have neither, havent had flash nor java installed on this win7 install which is 8 month old

So cant see all your funny youtube videos

[TechCoder]

i am trying to make automatic order filling app by just scanning products barcode

only thing i have trouble with is active state of warehouse items

which i am trying to resolve by exporting table from my company's software to excel

If you are making such a valuable piece of software that you can use yourself, it seems that it will have value to others, and, (it seems to me) your company should be happy that you are building such a thing.  

Therefore, they should give you access to the main database (at least a 'friendly' "dip" to grab a snapshot of status - with the exact same data as you can see now {i.e., no 'secret info', etc.})

Has to be much faster than the copy/paste method and certainly better for your app.

I'd suggest it to your manager, the IT manager and perhaps even a buddy you have in the IT dept..........  (politics in a corp environment is always tricky - you have to work it as you best see fit!)  In different companies, I've done all those, just to get what I need done to help my job (stinks, but that is the way it works...) - typically, the best method involves it becoming 'their idea'.... - you get what you need, they get the 'glory' - a WIN-WIN!

[Gianni]

is there no way to select more rows per time?
for example "click" on top left cell an then "shift click" on lower right cell?

[dickjones007]

If you are making such a valuable piece of software that you can use yourself, it seems that it will have value to others, and, (it seems to me) your company should be happy that you are building such a thing.  

Therefore, they should give you access to the main database (at least a 'friendly' "dip" to grab a snapshot of status - with the exact same data as you can see now {i.e., no 'secret info', etc.})

Has to be much faster than the copy/paste method and certainly better for your app.

I'd suggest it to your manager, the IT manager and perhaps even a buddy you have in the IT dept..........  (politics in a corp environment is always tricky - you have to work it as you best see fit!)  In different companies, I've done all those, just to get what I need done to help my job (stinks, but that is the way it works...) - typically, the best method involves it becoming 'their idea'.... - you get what you need, they get the 'glory' - a WIN-WIN!

 

love your thinking, but our IT manager is such a "specialist" who doesnt believe his own mother. i asked him about read-only rights to database and explained to him what i want to do and how useful it will be. he only gave some bullshit which summoned up to a "you dont really need that, do it the hard way".

[dickjones007]

is there no way to select more rows per time?

for example "click" on top left cell an then "shift click" on lower right cell?

 

i can select more/all items but they cannot be copied at once. there is no "copy" in context menu. ctrl-c works but only copies last clicked field.

[TechCoder]

love your thinking, but our IT manager is such a "specialist" who doesnt believe his own mother. i asked him about read-only rights to database and explained to him what i want to do and how useful it will be. he only gave some bullshit which summoned up to a "you dont really need that, do it the hard way".

That's one of the 'dangers' of going directly to the IT guys (sadly, most have an attitude of power...) and exactly why I suggested some other paths.  I'd try going through your manager - presuming you have permission and a requirement to gather it for your job, and permission from your boss to work on the program project - seems a more politically correct path ("It will help me in my job as well as X people in the group, etc.").

You really need to have some 'hard numbers' on the time/work savings and for how many, etc. to get this access, even though it isn't asking much, IMHO....  Write up some numbers, make a small report, etc. and give it to your boss.

Once the boss buys into the project and sees value, plus has a report in hand that shows how much the group will save (thus, handing him/her all the data they need to go further with no effort on their part and getting all the credit....... - yeah, becoming the boss' idea....), then IT has no option but to open a link for you.  

Also, a bit of 'power' for your end on the report - as you are not asking for anything other than the data already shown, the command you need is nothing other than the one being used now, so there should be less than 10 minutes work on the part of IT (i.e., no unreasonable burden).  All you need is the sql command, or a program that does that and puts the data to an external flat file (if they bring up the argument of giving you access directly into the database, etc.) and it is the same data, just output in a different manner where you can then process it fast and give additional value to 'millions' (ok, maybe not, but quantify it!)

Oh, and since you have already taken it to IT, you can use that info in your report - in a positive way, though!  What were the IT guy's issues (probably feeling like it was a lot of extra work to them, etc.)?  Make sure you address anything they said to you, whatever it was.  That way, when the boss takes "his" idea to the IT guy, he already has the answers to those issues, written in a nice report.....  (i.e., don't give IT any way to let the boss look bad - that backlashes pretty heavily!)

Looking at your program, I think you might speed it up slightly going a different route,  though not likely worth the effort to even evaluate different methods.  The only way to 'significantly' speed it (which is what you need to make it worthy of doing the project at all) is to get direct access to the data.  So, before you mess with the code any more, use your time to write a comprehensive report that the boss can take to the big boss - that is the best use of your time, and the way you will get the results.

[gruntydatsun]

love your thinking, but our IT manager is such a "specialist" who doesnt believe his own mother. i asked him about read-only rights to database and explained to him what i want to do and how useful it will be. he only gave some bullshit which summoned up to a "you dont really need that, do it the hard way".

 

What I'm hearing your IT manager say is "I don't know how to do that or even what you're talking about and will not be admitting it to you today".

[czardas]

Automating Excel is a slow process. Can you not use an array for the data manipulation? Save the data as csv and then open with Excel to save as xls, or whatever format they use nowadays.