How do I import a CSV file with a few buttons pushed?

[The_Big_Tuna]

We are thinking about purchasing Macro Scheduler and it looks great...but may be out of our league from a programming standpoint. I wanted to know if this was possible and how I would do it (I will name all my kids after you if we can do it soon). Basically...here are the details:

I have a CSV file that has a list of accounts...each on a new line. I would be using QuickBooks and I need to enter the information for each account. When I am in QB I would start the macro, it would enter the account name >
tab 8 times
space bar (to make a selection)
Alt+S
Pause approximately 10-15 seconds (until a specific button comes up) then hit Enter
Pause approximately 10-15 seconds (until a specific button comes up) then hit Enter
Alt+C
Repeat process with the next account in the CSV file.

I may be missing a key to be pressed or a tab…but I would imagine it would pretty simple change.

Any help would be greatly appreciated!

[Marcus Tettmar]

Something like this:

Code: Select all

Let>theFile=d:\test.csv

//Initialise file loop counter
Let>k=1
Label>ReadFileLoop
//read line from file
ReadLn>theFile,k,record
//if not at end, proceed
If>record<>##EOF##
  //Split record into separate fields
  Let>comma=,
  Separate>record,comma,field

  //we now have field_1, field_2, field_3 .. field_count

  //** So in this section you can send each field to the target application
  //** with keystrokes .... e.g. assume account name is field_1
  SetFocus>ReplaceWithQuickBooksWindowName
  Send>field_1
  Press Tab * 8
  Wait>0.2
  Send>SPACE
  Wait>0.2
  Press ALT
  Send>s
  Release ALT
  Wait>15
  Press Enter
  Wait>15
  Press Enter
  Press ALT
  Send>c
  Release ALT
  //** END send data to window

  //continue to next line
  Let>k=k+1
  Goto>ReadFileLoop
Endif

Note that this is completely untested, I do not have Quickbooks. But hopefully gives you the idea. You probably don't even need the Separate part, since your description suggests each line only has one field. The above will split comma separated values on each line into separate variables. The key send part you will need to determine for yourself since I am only going off your description and do not have Quickbooks to test with. Ensure you focus the correct window, and if you cause a change of window use WaitWindowOpen ... hopefully you get the idea from the above.

If you have not already read this, then please do before attempting anything else:
http://www.mjtnet.com/blog/2006/01/17/h ... on-script/

And also be sure to read the beginner's scripting guide in the help file.

[The_Big_Tuna]

Marcus,

thank you SO much for the help...but I need a little bit more. Because you were so responsive I went ahead and bought the MS Pro SME package! Here is exactly what I need:

I need to put the CSV file with all the accounts somewhere (i.e. the C drive), open my billing application, then run the macro. The macro needs to do these steps:
Enter the account name on the first line of the CSV file
Tab 12 times
Hit the space bar
Alt+S
Wait 8 seconds
Hit Enter
Wait 5 seconds
Alt+C
enter the account name on the next line of the CSV file and repeat the process until every account has been entered.

I cant thank you enough for helping me with this!

[Marcus Tettmar]

The code I posted above is very close to what you want to do, sounds like just the keystrokes are different? These keystrokes would be:

Press Tab * 12
Send>SPACE
Press ALT
Send>s
Release ALT
Wait>8
Press Enter
Wait>5
Press ALT
Send>c
Release ALT

You will need to add the correct SetFocus line before sending the keystrokes, and you may need to play with the timing/add some waits.

In terms of reading from the CSV file that would be exactly the same as shown in my previous post. What I have provided will get you 95% there. The rest is down to you!

[The_Big_Tuna]

Marcus,

Hmmmm...I (after pulling my hair out a bit because I am new to this) tried to run the script that you wrote (again...I cant thank you enough) and here is what I noticed:
#1 - The Account Name that is listed in the CSV file is different every time. Every line is a new account and the accounts will change every month. You listed the account as field_1. Please note that it cannot be a hard set account name in the code. The CSV file generates the account name to paste it in the QuickBooks application.

#2 - I run the script and it says: Specified Window "ReplaceWithQuickBooksWindow" not present. Any subsequent key sends in script may cause exceptions. Thoughts?

What am I doing wrong? I really hope I can get this program working as I love the concept of it and I foresee many in house uses for it.

Please help!

[Marcus Tettmar]

Err, my code was example code only that will NOT work without some modification. field_1 simply represents the first field of each record. Nothing is hardcoded there. The code reads each line from the file, and splits that line up into separate fields (using the comma as the delimiter - you did say it was CSV). So field_1 is a variable holding the first value in the current line. But as noted in a previous post of mine, it may be that your file doesn't have more than one field - just the account name on it's own - in which case you can simplify this and do not need the CSV splitter. But you said it was CSV (Comma Separated Values).

ReplaceWithQuickBooksWindow should be self explanatory! Replace it with the correct window title for your quickbooks window (or whatever window you are wishing to send the keystrokes to for that matter).

Just trying to help.

[The_Big_Tuna]

Soooooo close. I think the
Let>comma=,
Separate>record,comma,field
is kinda messing things up because there are no other fields. Just an account name. Should I get rid of that line?

Also, I cant figure out what the SetFocus line should be. It is the Customer Payment window inside QB.
Any ideas?

[Marcus Tettmar]

Yes, remove those lines and replace "field_1" with "record" - so all it is doing is using the full line from the file:

ReadLn>theFile,k,record
...
...
...
Send>record

>Also, I cant figure out what the SetFocus line should be.
>It is the Customer Payment window inside QB. Any ideas?

What is the window title of that window? What does it say in the title bar? The title bar is the uppermost section of the window - it is usually blue and has some text in it - that text is the window title. That is what you use in the SetFocus line to tell it to ensure that window is focused.

[The_Big_Tuna]

(poking my eyes out)

I cant figure this out. The window name is Receive Payments. I have tried that and ReceivePayments but it still tells me the window is not present.

It also does some strange stuff such as tabs to the end of the macro script and stops. It also enters the first account name in different areas.

Here is something you should probably know; I use a CSV file that was from Excel. When I open teh CSV file in notepad, there are no comments. Each account is on a different line.

Here is the script I have. Am I doing something wrong?
Let>theFile=c:\cc.csv

//Initialise file loop counter
Let>k=1
Label>ReadFileLoop
//read line from file
ReadLn>theFile,k,record
//if not at end, proceed
If>recordReceive Payments
Send>record
Press Tab * 12
Send>SPACE
Press ALT
Send>s
Release ALT
Wait>8
Press Enter
Wait>5
Press ALT
Send>c
Release ALT
//** END send data to window

//continue to next line
Let>k=k+1
Goto>ReadFileLoop
Endif

[Marcus Tettmar]

Well there is an error with your If statement and no SetFocus line. Fixing that would give you:

[code]Let>theFile=c:\cc.csv

//Initialise file loop counter
Let>k=1
Label>ReadFileLoop
//read line from file
ReadLn>theFile,k,record
//if not at end, proceed
If>record##EOF##

SetFocus>Receive Payments
Send>record
Press Tab * 12
Send>SPACE
Press ALT
Send>s
Release ALT
Wait>8
Press Enter
Wait>5
Press ALT
Send>c
Release ALT
//** END send data to window

//continue to next line
Let>k=k+1
Goto>ReadFileLoop
Endif[/code]

But whether this will work or not I cannot possibly tell you without looking at your screen (or having a copy of your .csv file, a copy of QuickBooks and an indentical QuickBooks setup - none of which I have).

I'm assuming you have read the getting started guides and understand what the code above does? If so and you are still not getting anywhere then please contact support and we'll do a remote desktop session so that I can see your screen and we can work through it and get you on your way.