Sign in to follow this  
Followers 0
JerMu

Macro Inconsistant

11 posts in this topic

#1 ·  Posted (edited)

Ok I have a macro going that simply cuts and paste data from one excel spreadsheet (downloaded info) to another to keep it up to date. The macro works fine and I think all the lines are correct because it has ran perfectly on many occasions, BUT it seems that sometimes it wants to run perfect and other times it wants to do crazy things like not save the file once I exist - which causes me to lose the info, and other times it wants to skip sections entirely. Can someone enlighten me on whether this is a normal occurance with autoIT, or if there is a common fix for this type prob, or just what I am doing wrong.

I think its the occasional sleep(XXX) command in combination with system lag that might be causing the break to not be fast enough for certian functions to complete, like a save...but Im not sure. I'll provide some of my macro to let you see what it looks like and hopefully someone can show me where I am going wrong.

**By the way I am a total newbie so please don't flame me if I am doing something one way that should be done another**

Run('"C:\Microsoft Office\OFFICE\EXCEL.EXE" "C:\Documents and Settings\User\Desktop\StockTemp\SpreadSheet1.xls"')

Sleep(250)

Send("^{Home}")

Send("^{PGUP 10}")

Send("^{Home}")

Send("^{PGDN 2}")

Send("^{Home}")

Send("{DOWN}")

Send("{RIGHT}")

Send("{SHIFTDOWN}")

Send("{DOWN 11}")

Send("{RIGHT 8}")

Send("{SHIFTUP}")

Send("^c")

$s = ClipGet()

If @error Then

MsgBox(16, @ScriptName & " - Error", "Error reading Clipboard")

Exit

EndIf

Send("^{PGUP}")

Send("^{Home}")

Send("{DOWN}")

Send("{RIGHT}")

Send("^v")

Send("^{PGDN}")

Send("^{Home}")

Send("{DOWN 18}")

Send("{RIGHT}")

Send("{SHIFTDOWN}")

Send("{DOWN 10}")

Send("{RIGHT 8}")

Send("{SHIFTUP}")

Send("^c")

$s = ClipGet()

If @error Then

MsgBox(16, @ScriptName & " - Error", "Error reading Clipboard")

Exit

EndIf

Send("^{PGUP}")

Send("^{Home}")

Send("{DOWN 18}")

Send("{RIGHT}")

Send("^v")

***this is where the macro simply takes older info and shifts it over to anohter spread sheet in the same workbook pushing old unwanted data out, this repeats for a few cycles and then it goes into another series where it downloads the new data and overwrite the "current day" sheet. it reads as such:***

URLDownloadToFile("http://www.SpreadsheetUpdate.csv", "C:\Documents and Settings\User\Desktop\SpreadsheetUpdate.csv")

Sleep(250)

If FileExists("C:\Documents and Settings\User\Desktop\SpreadsheetUpdate.csv") Then

Run('"C:\Microsoft Office\OFFICE\EXCEL.EXE" "C:\Documents and Settings\User\Desktop\SpreadsheetUpdate.csv"')

Else

MsgBox(4096,"", "File does not exist yet")

EndIf

Sleep(250)

Send("^{Home}")

Send("{DOWN}")

Send("{RIGHT}")

Sleep(250)

Send("{SHIFTDOWN}")

Send("{DOWN 11}")

Send("{RIGHT 8}")

Send("{SHIFTUP}")

Send("^c")

$s = ClipGet()

If @error Then

MsgBox(16, @ScriptName & " - Error", "Error reading Clipboard")

Exit

EndIf

Sleep(250)

ProcessClose("EXCEL.EXE")

Sleep(250)

Send("y")

Sleep(250)

Run('"C:\Microsoft Office\OFFICE\EXCEL.EXE" "C:\Documents and Settings\User\Desktop\StockTemp\StockAnalysis.xls"')

Sleep(250)

Send("^{Home}")

Send("^{PGDN 10}")

Send("^{Home}")

Send("{DOWN}")

Send("{RIGHT}")

Send("^v")

Sleep(250)

Send("^w")

Sleep(250)

Send("y")

Sleep(250)

ProcessClose("EXCEL.EXE")

***this simply just take info from an excel file downloaded and copies it to the spread sheet where my formulas are***

Sorry for the lenght but I just thought I would let you guys see what i got..

I use "send" commands for the copy/paste and movement thought the excel workbook...its the only way I knew to do it.

Should I make more pauses to compensate for system lag, I just can't see there being lag that would interfere with it like it does.. I have an 2600 xp with 1 gig of Ram...win XP...

I hope someone sees the prob. Just doesn't make sense why sometimes it works like gold and others it doesn't.

Edited by JerMu

Share this post


Link to post
Share on other sites



The problem is that you don't wait for any window titles to become active, or test for an indication of completion at each stage. Each time you do something on your computer, even if it is the exact same task, it won't always take the same amount of time. Many factors play into this, such as RAM usage, pagefile usage, uptime, and others. You can't ever count on any specific delay working 100% of the time.

You can use the WinWait, WinWaitActive, and WinExist commands to test for windows, and changes of title. You can also use ControlGetText, or read the text from the status bar to detect various messages. This is a more reliable method since it waits to insure that your actions are complete instead of just sending keys regardless of where the application is in its process.


[font="Optima"]"Standing in the rain, twisted and insane, we are holding onto nothing.Feeling every breath, holding no regrets, we're still looking out for something."[/font]Note: my projects are off-line until I can spend more time to make them compatable with syntax changes.

Share this post


Link to post
Share on other sites

Ok, I'll give this a go, but Im not exactly sure how these commands work...I'll let you know if I have anymore trouble.

thanks for the info

Share this post


Link to post
Share on other sites

Ok I feel now that my macro is much more stable in terms of consistancy...however I am I curious is there a way to save a file without having to sue the ctl+s keyboard function? if there were a function that would either wait on that command or a command in it of itself that would save the file I am working on and not let he macro continue until the file was saved then I think my macro is complete...right now everything else works perfect except where I have a save going...sometimes it saves sometimes it doesn't.

Share this post


Link to post
Share on other sites

Ok I feel now that my macro is much more stable in terms of consistancy...however I am I curious is there a way to save a file without having to sue the ctl+s keyboard function?  if there were a function that would either wait on that command or a command in it of itself that would save the file I am working on and not let he macro continue until the file was saved then I think my macro is complete...right now everything else works perfect except where I have a save going...sometimes it saves sometimes it doesn't.

<{POST_SNAPBACK}>

That's not really within autoit's scope, I bet that Excel's VBA launguage can do it though.

"I'm not even supposed to be here today!" -Dante (Hicks)

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

Try winmenuselectitem to select file>save

Edited by this-is-me

Who else would I be?

Share this post


Link to post
Share on other sites

Try winmenuselectitem to select file>save

<{POST_SNAPBACK}>

That's just the same as using ctrl-s and doesn't tell autoit when saving is done. The only thing I can think of doing would be pixel checking for a change at the point in the status bar where the bar goes over to show that it's all saved... Which is what PixelChecksum does...

"I'm not even supposed to be here today!" -Dante (Hicks)

Share this post


Link to post
Share on other sites

but ctrl+s can be circumvented accidentally since it is global. winmenuselectitem is specific to the application.


Who else would I be?

Share this post


Link to post
Share on other sites

ok I'll try the winmenuselectitem to see if it works a bit better, as far as the Pixel thing the only commands I see in the help are PixelSearch, PixelGetColor, and PixelCoordMode....I don't understand how these could be used...can you explain this function a bit more and how I might use it to know when a file I saved...I think what you are saying is this function will detect the status bar for when it has indicated that the save function is done, but I don't know how to set this up or even if I'm correct in what you are suggesting it do...

thanks

Share this post


Link to post
Share on other sites

Ok I just added WinMenuSelectItem and the copy/paste/save didn't seem to work...I read a *note* in the help file stating that most microsoft menus are infact toolbars pretending to be menu bars and that this function will only work on standard menus...any idea how to get around this, or should I jsut stick with the Send("^c") = copy, Send("^v") = paste, etc..?????

Share this post


Link to post
Share on other sites

Yea, you might want to do a winactivate and then the send to make sure the right window is in focus.


Who else would I be?

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  
Followers 0