Please help with autoit script needing human intervention

[rcareno]

Hello all,

So i have an autoit script that copy's certain fields from a notepad document and paste's them into an excel 2007 document

I set the script to do an alt tab via:

send("!{TAB}")

The problem is, sometimes the script stops working and i need to manually push ALT+TAB for the script to continue doing it's thing.

Could you look over my script and see if there is anything that could be modified to help the script to work properly?

I added Sleep(200) before and after the alt+tab to see if it would help, also tried sleep(750) before and after with same issue.

Any advice would be helpful

Thanks

excel-notepad-script.au3

[seandisanti]

Hello all,

So i have an autoit script that copy's certain fields from a notepad document and paste's them into an excel 2007 document

I set the script to do an alt tab via:

send("!{TAB}")

The problem is, sometimes the script stops working and i need to manually push ALT+TAB for the script to continue doing it's thing.

Could you look over my script and see if there is anything that could be modified to help the script to work properly?

I added Sleep(200) before and after the alt+tab to see if it would help, also tried sleep(750) before and after with same issue.

Any advice would be helpful

Thanks

excel-notepad-script.au3

a sample of your text would be helpful too, i've got a couple of ideas for you

[4ggr35510n]

Hm, for like a second I've rly wanted to help you... Then I saw this terrible mess, so only thing I can say are two tips and helpfile recommendation.

1. Little Tip:

WinWaitActive("Run")
Send("excel{Enter}")

Equals to

Run("excel.exe")

2. Little Tip:

$i = 0
while $i <= $emails
$i = $i + 1
; do smth
WEnd

Equals to

For $i = 0 to $emails
; do smth
Next

3.You should use Excel.au3 and File.au3 build-in UDF's.

Start from figuring out _ExcelWriteCell example.

Everything is in helpfile, with g00d, clear examples.

About File, the most needed function will be FileReadLine (also start education from example)

I wish you best luck, beginnings are always hard ;]

[seandisanti]

if you can include a copy of your text file (and perhaps your spreadsheet), i can show you much better ways to do what you want

[rcareno]

Thanks 4ggr35510n for the tips, very helpful...

Attached is a sample text file containing 21 emails.

I store the text file in the root of the C: drive as C:\cribtentexport.txt and start the script which makes an excel file c:\cribtentfinal.xlsx

and copy and pastes all the info over to the excel.

cribtentexport.txt

Thanks

[seandisanti]

Thanks 4ggr35510n for the tips, very helpful...

Attached is a sample text file containing 21 emails.

I store the text file in the root of the C: drive as C:\cribtentexport.txt and start the script which makes an excel file c:\cribtentfinal.xlsx

and copy and pastes all the info over to the excel.

cribtentexport.txt

Thanks

ok, this may take me a couple of minutes because i'm handling calls in a call center at the same time. what version of excel are you using? and do you want this to go to a new sheet when the script is run, or appended to another sheet?

[rcareno]

It's fine to do a new excel sheet every time as i will be doing lists on a weekly basis and would like a separate list for each week.

I am using Microsoft Office Excel 2007 (12.0.6535.5002) SP2 MSO

No rush, thank you for the help.

[omikron48]

When Send sometimes seems to fail, it may be the case that the keys Send is sending are being pressed too fast for the program to handle. Try adding this at the top of your script:

Opt("SendKeyDownDelay", 50)

That is usually long enough for most programs to correctly recognize and handle sent keys.

[seandisanti]

It's fine to do a new excel sheet every time as i will be doing lists on a weekly basis and would like a separate list for each week.

I am using Microsoft Office Excel 2007 (12.0.6535.5002) SP2 MSO

No rush, thank you for the help.

are your text files generated programatically? just a quick side note, you could save yourself a lot of headaches getting the info in csv form since you could then just open the csv in excel and have what you need with 0 extra work, but if that's not possible, a couple of regular expressions and com objects should be just the ticket, assuming you're able to enable macros in excel.

[rcareno]

When Send sometimes seems to fail, it may be the case that the keys Send is sending are being pressed too fast for the program to handle. Try adding this at the top of your script:

Opt("SendKeyDownDelay", 50)

That is usually long enough for most programs to correctly recognize and handle sent keys.

I made a couple minor adjustments per the previous recommendations, and also added this line into my script.

I am no longer having any issues.

Also, my text file is being exported from microsoft outlook 2007 via highlighting a long list of e-mails and doing a save as .txt....

Tried exporting as CSV but it seemed to be more work to write a script to do what i needed.

Thank you all for the fast responses, much appreciated.

[seandisanti]

almost done, just have one little loop to do when i'm done to actually fill the cells at the commented portion(with trimmed contents of the arrays because i have a trailing backslash because i suck at regex). and commenting so that it will help you learn instead of just doing the job for you

expandcollapse popup

#include<file.au3>;so that we can use _FileReadToArray
#include<array.au3>;so that we can use _ArrayToString

dim $FullTexta,$fulltexts,$date,$name,$country,$street,$city,$state,$zip,$tel,$email,$quantity,$corr,$comments;declaring variables is always a good idea
_FileReadToArray("c:\cribtentexport.txt",$FullTexta);creates an array
$fulltexts=_ArrayToString($FullTexta,"\");turns array into a backslash delimited blob
;the regular expressions below this point create arrays of each of the pieces of data
;that you're looking for based on the backslash delimiters (which was a horrible choice of delimiter on my part but i'm not redoing it)
$date = StringRegExp($fulltexts,'(?:Sent:)(.*?\\)',3)
$name = StringRegExp($fulltexts,'(?:Name\\\|.*?\\\|)(.*?\\)',3)
$country = StringRegExp($fulltexts,'(?:Country\\\|\\\|)(.*?\\)',3)
$street = StringRegExp($fulltexts,'(?:Street Address\\\|\\\|)(.*?\\)',3)
$city = StringRegExp($fulltexts,'(?:City\\\|\\\|)(.*?\\)',3)
$state = StringRegExp($fulltexts,'(?:State\\\|\\\|)(.*?\\)',3)
$zip = StringRegExp($fulltexts,'(?:Zip Code\\\|\\\|)(.*?\\)',3)
$tel = StringRegExp($fulltexts,'(?:Telephone\\\|\\\|)(.*?\\)',3)
$email = StringRegExp($fulltexts,'(?:Email \\\|\\\|)(.*?\\)',3)
$quantity = StringRegExp($fulltexts,'(?:Quantity\\\|\\\|)(.*?\\)',3)
$corr = StringRegExp($fulltexts,'(?:Consumer or Retailer\\\|\\\|)(.*?\\)',3)
$comments = StringRegExp($fulltexts,'(?:Comments\\\|\\\|)(.*?\\)',3)

$oEx = ObjGet("","excel.Application");If you have excel open, this grabs ahold of the application to make it do our bidding
$oWb = $oEx.Workbooks.Add;creates a new workbook just incase the one you were working on wasn't intended to be for this project
If IsObj($oWb) = 0 Then;if you didn't have excel open it will error out (you could also have it create an instance but it's easy i'll let you
    MsgBox(0,"Error","Failed to create workbook, exiting.  open excel and try again")
    Exit
EndIf
$oWS = $oWb.ActiveSheet;grabs the active sheet in the new workbook
;then all the headers are added to the first row
$oWS.Range("a1").Formula = "Date"
$oWS.Range("b1").Formula = "Name"
$oWS.Range("c1").Formula = "Country"
$oWS.Range("d1").Formula = "Street Address"
$oWS.Range("e1").Formula = "City"
$oWS.Range("f1").Formula = "State"
$oWS.Range("g1").Formula = "Zipcode"
$oWS.Range("h1").Formula = "Telephone"
$oWS.Range("i1").Formula = "Email"
$oWS.Range("j1").Formula = "Quantity"
$oWS.Range("k1").Formula = "Consumer or Retailer"
$oWS.Range("l1").Formula = "Comments"

;this loop adds the data from the various arrays into their coloumns
For $x = 2 to UBound($date);starting with an iterator of 2 because we do not want to overwrite headers
    $oWS.Range("a" & $x).Formula = StringTrimRight($date[$x-2],1);string trim gets rid of the trailing backslash i was too lazy to
    $oWS.Range("b" & $x).Formula = StringTrimRight($name[$x-2],1);continue fighting with
    $oWS.Range("c" & $x).Formula = StringTrimRight($country[$x-2],1);and we are subtracting 2 from $x because even though the spreadsheet
    $oWS.Range("d" & $x).Formula = StringTrimRight($street[$x-2],1);needs to be populated at the second row, the first element of the array
    $oWS.Range("e" & $x).Formula = StringTrimRight($city[$x-2],1);is 0
    $oWS.Range("f" & $x).Formula = StringTrimRight($state[$x-2],1)
    $oWS.Range("g" & $x).Formula = StringTrimRight($zip[$x-2],1)
    $oWS.Range("h" & $x).Formula = StringTrimRight($tel[$x-2],1)
    $oWS.Range("i" & $x).Formula = StringTrimRight($email[$x-2],1)
    $oWS.Range("j" & $x).Formula = StringTrimRight($quantity[$x-2],1)
    $oWS.Range("k" & $x).Formula = StringTrimRight($corr[$x-2],1)
    $oWS.Range("l" & $x).Formula = StringTrimRight($comments[$x-2],1)
Next
$oWb.SaveAs("c:\cribtentfinal");this is to save your workbook with the name that you want

***edit***

updated the code with that loop and plenty of comments. This works for the sample data and should work for the real stuff too. i went with the backslash delimiter because i figured it couldn't come up in the actual data to give me additional headaches with the regular expressions -totally forgetting that it's the escape character in regular expressions which gave me a whole different headache, but i stuck with decision and just coded around my ineptitude. you just have to have an instance of excel running, and the text file created (but not open) and you'll be all set. try it out and ask any questions you may have. this will work faster than the way you were doing it, and will not interfere at all with your keyboard or mouse.

***edit2***

fixed the workbok typo.

Edited August 17, 2010 by cameronsdad

[seandisanti]

see, that's why the smart people don't just up and write code for someone without some previous post history, they end up not even coming back to see it. oh well, time you've enjoyed wasting and all that.

[Bert]

It wasn't a total waste. You got to work with StringRegExp. You said yourself that you were not good at it. Practice always makes your coding better.

[seandisanti]

It wasn't a total waste. You got to work with StringRegExp. You said yourself that you were not good at it. Practice always makes your coding better.

i enjoy problem solving, so it was fun, and i still hate regex. i only ever pick up enough to accomplish the goal and then shake it right out of my head once i'm done. lol

[rcareno]

It definitely was not a waste of time...I have just been very busy. I really appreciate all of your help cameronsdad. I am going to test it out right now. I will report back.

Thanks again...looks much cleaner than what I wrote

Ps...I had gotten my original script to work, but it was very slow and was not working efficiently (would work properly half the time)

[omikron48]

The reason your script may be slow is probably because of the 50ms send key down delay. If it's set to that and you are sending a lot of keys, it's going to take you a good while typing stuff. You only really want to use Send for menu interaction, like hotkeys or shortcuts.

For typing text into something that also accepts copy/paste commands, it's more practical to use the clipboard for transferring text from memory to the field/document, as opposed to using send for the whole thing.

You can also try lowering the send key down delay then observe if the program is still handling the sent keys well enough.

As for your 50-50 successful execution, probable causes may range from improper timing for program interactions to incorrect processing of data.

[seandisanti]

It definitely was not a waste of time...I have just been very busy. I really appreciate all of your help cameronsdad. I am going to test it out right now. I will report back.

Thanks again...looks much cleaner than what I wrote

Ps...I had gotten my original script to work, but it was very slow and was not working efficiently (would work properly half the time)

awesome, sorry for giving up on you, but after a couple of days, i thought you'd just opted to go with the your original method. the new way works with the test data, if it doesn't work with real data, may have to have you pm me some real data to tweak it a little bit, but i think that it will

[rcareno]

Ok, So it is not working for some reason.

Row 1 fills fine with no problem

But on row 2 in excel, it stops at the name column.

Attached is a small test cribtentexport.txt file

I filled out fake information, but it is very close to some of the e-mails

If you could help it would be greatly appreciated.

[seandisanti]

Attached is a small test cribtentexport.txt file

I filled out fake information, but it is very close to some of the e-mails

now when you say "attached"...

[rcareno]

deleted

Edited February 13, 2017 by rcareno