Macro Inconsistant

[JerMu]

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 August 11, 2004 by JerMu

[pekster]

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.

[JerMu]

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

[JerMu]

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.

[emmanuel]

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.

[this-is-me]

Try winmenuselectitem to select file>save

Edited August 13, 2004 by this-is-me

[emmanuel]

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

[this-is-me]

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

[JerMu]

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

[JerMu]

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

[this-is-me]

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