Jump to content
Sign in to follow this  
dickjones007

need help with speeding up the script

Recommended Posts

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

Share this post


Link to post
Share on other sites
BrewManNH

What are you trying to accomplish?


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


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

Share this post


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

Share this post


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

Share this post


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

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites
Chimp

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?


small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


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

Share this post


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

Share this post


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

  • Like 1

Share this post


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

  • Like 1

Share this post


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

Share this post


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
Sign in to follow this  

×