Cartwheels Excel Processing

[terrypin]

I'm still a bit unclear as to what you're trying to do. But if I've understood your last post correctly (in the other long thread about another subject) then you have a worksheet like this:

 

 

If you just want to copy this to another application then I don't understand why you are trying to associate ME variables with every cell? (Which would in any event limit you to 99.) Why can't you use a simple macro like the following?

 

// TEMP - Cartwheels Query
// Go to cell A1
Text Type: <CONTROL>ga1<ENTER>
Delay 100 Milliseconds
// Select entire column, say 100 cells
Text Type: <SHIFT><CONTROL><ARROW DOWN>
Delay 100 Milliseconds
// Copy these 100 cells to clipboard
Text Type: <CONTROL>c
Delay 100 Milliseconds
// Activate Notepad (like your accounting application) and paste the cells 
Activate Window: "Untitled - Notepad"
Wait For Window Title: "Notepad"
Text Type: <CONTROL>v
Delay 100 Milliseconds
Macro Return

 

 

<REM2:TEMP - Cartwheels Query><REM2:Go to cell A1><TEXTTYPE:<CONTROL>ga1<ENTER>><MSD:100><REM2:Select entire column, say 100 cells><TEXTTYPE:<SHIFT><CONTROL><ARROW DOWN>><MSD:100><REM2:Copy these 100 cells to clipboard><TEXTTYPE:<CONTROL>c><MSD:100><REM2:Activate Notepad (like your accounting application) and paste the cells ><ACTIVATE2:Untitled - Notepad><WAITWIN2:000010:000000:Notepad><TEXTTYPE:<CONTROL>v><MSD:100><MRETURN>

 

--

Terry, East Grinstead, UK

[terrypin]

I meant to add that you could also, of course, copy these across in groups or even individual cells to separate locations in your accounting application. And assign each to an ME variable if you wish (up to 99), using the familiar Variable Set String %T1% from Clipboard command.

 

Anyone know why the Edit button isn't available on my original post please? Never noticed that before...

 

--

Terry, East Grinstead, UK

[stevecasper]

Anyone know why the Edit button isn't available on my original post please? Never noticed that before...

 

--

Terry, East Grinstead, UK

Did you scroll all the way to the right? Personally I love these forums, but sometimes the interface is a pain in the butt.

I hate having to scroll off to the right and back again just to read a post. For my posts, I take the extra step of entering

carriage returns on each line so that my own text doesn't go off into the far-right distance...

 

But still, even then, the Edit/Reply/Quote buttons are clear over at the end.

[stevecasper]

Thank you so much for helping me out late on a saturday after long day!

I set this up exactly like you did, but it just seems to sit there and not do anything. When I originally tried to save it as a csv, Excel gives me a message that my worksheet may contain features that are not be compatable with csv.

 

Ultimately though I don't know if this will work because I was really planning using this for a workbook with multiple sheets with lots of info on each sheet. What I do is use ME to take payroll info out of Excel and to put it into our finky accounting software (it's good but difficult to work with sometimes). It is much easier to get it into excel to start with.

 

I have ME copy four cells then toggles to accounting program types them in then toggles back. This actually works pretty well, but I was thinking that it would be far more effeceint to copy everything out of excel first then go to the accounting program and type it all in . This can up to 80 to 100 cells worth of info.

 

Then there is the fact that info from Excel has to be trimed. This all may be more trouble than it is worth. I didn't think it would be so hard when I first started.

 

Thanks again for the help. ME is really a great program! I will be using it a lot.

 

I was hoping to start with a simple but understanable senario then adapt it to a more complicated macro with some controlling "logic" .

I do something similar at work. I receive (usually on a daily basis) an Excel spreadsheet list of account numbers

for clients, an associated ID Number, and various other information about the account. It's my job to take the

account# and the ID# and enter the client's information into another program that helps track specific account

behavior (I work in the Fraud Management group of a bank).

 

What I usually do is manually highlight the Acct# list and save it to a .txt file, then I highlight the associated ID# list

and save it to a second .txt (I actually do the saving with a macro, but I'm trying to keep things relatively simple here

and stick with the main theme).

 

What my main macro for this job then does is:

1) Activates and accesses the recipient program

2) Retrieves the first line of the Acct# file

3) Retrieves the first line of the ID# file

4) Enters all pertinent information into the appropriate fields in the recipient program

5) Rinses and repeats as necessary (retrieving the second lines, etc.)

 

The recipient program, in this case, is an annoying little Dos-looking piece of programming, and so I have had to do

a lot of work-around stuff with my macro to get it to work right - so I understand how difficult it can be when you are

working with a program that isn't nearly as convenient as Excel or other smooth-working business-practical software.

 

The basic idea for this macro goes something like this:

 

I created two .txt files, one a list of "Account Number 001" through "Account Number 050", the other a list of "ID

Number A1" through ID Number A50". I saved them as, respectively, testaccountnumber.txt and testidnumber.txt

in my Macro Test folder of my C: drive.

 

This is the macro as I have it built:

Variable Set Integer %N1% to 1
Text File Begin Process: "testaccountnumber.txt"
 Text File Begin Process: "testidnumber.txt"
Activate Window: "Untitled - Notepad"
Text Type: %T1%<TAB>%T2%<ENTER>
Variable Modify Integer: Inc (%N1%)
Break
 Text File End Process
Text File End Process

<IVAR2:01:01:1><BTFBEG:001:000001:000000:C:\Users\Steven\Documents\Macros\Macro Test Files\testaccountnumber.txt><BTFBEG:002:N00001:000000:C:\Users\Steven\Documents\Macros\Macro Test Files\testidnumber.txt><ACTIVATE2:Untitled - Notepad><TEXTTYPE:%T1%<TAB>%T2%<ENTER>><NMVAR:08:01:0:0000001:0:0000000><BREAK><BTFEND><BTFEND>

Here is the script editor again (with Explanatory Remarks this time):

// First I set N1 to 1 for reasons to be explained later.
Variable Set Integer %N1% to 1
// I begin the process with the account numbers being saved to T1,
// set to start with the first line and continue till finished.
Text File Begin Process: "testaccountnumber.txt"
 // Next I begin processing the ID Numbers (saved into T2), 
 // however I set it to start at N1 (this is why N1 is set to 1)
 Text File Begin Process: "testidnumber.txt"
// Now I run the bulk of my macro, entering info into various fields. 
// For the purpos of this example, the "bulk" consists of activating a notepad and
// typing T1 and T2 into it.
Activate Window: "Untitled - Notepad"
Text Type: %T1%<TAB>%T2%<ENTER>
// Now I increment N1 (now N1 will = 2 so when the process repeats,
// T2 will pick up the second ID Number)
Variable Modify Integer: Inc (%N1%)
// Now I break the "inside" process.  Without this break,
// T1 would be stuck on the first account number while T2 continued to increment.
Break
// End both processes (even though the break makes the "inside" process end,
// the code requires an end process).
 Text File End Process
Text File End Process

[Cartwheels]

Edit: I just posted this, but it took me a long time and had not checked for recent posts, So I need review your post Steve. Thanks guys!

 

I wish I could paste columns of info into the accounting program, unfortunately it it does not allow any paste what so ever. From excel it must be typed from the clip board one at a time. So I take 5 cells worth at a time, just because that is the way it is organized. I assign a seperate variable to those 5 cells of info. Then it gets typed into the Pay Roll Program and repeat till it gets to the end. I have to have logic in there to controll when it gets to the end and other things as well.

 

Since this is a new thread I'll mention again what I'm trying to do. I currently have a macro doing the above pretty well. It just has to toggle back and forth quite a bit. So I was thinking that it might be more efficient to copy as much as I can from excel and toggle over to the payroll program and type it in. Each cell would have to its own variable. I believe the info from Excel also has to have the formatting trimmed out before you type it out.

 

The info has to land in the right spots so I was thinking I could start with T1 and increment up from there, then the same when it is being typed out in the Pay Roll Program.

 

Here is what the excel spreadsheet looks like. The yellow area plus the rate area which is one column to the right is the info I transfer to the other program.

 

Edit: I should also mention I have to enter in the P/R Program one line at a time, in other words "Code", "Hours", "Field", "Phaze", "Rate". I can't do columns.

 

[stevecasper]

Edit: I just posted this, but it took me a long time and had not checked for recent posts, So I need review your post Steve. Thanks guys!

 

I wish I could paste columns of info into the accounting program, unfortunately it it does not allow any paste what so ever. From excel it must be typed from the clip board one at a time. So I take 5 cells worth at a time, just because that is the way it is organized. I assign a seperate variable to those 5 cells of info. Then it gets typed into the Pay Roll Program and repeat till it gets to the end. I have to have logic in there to controll when it gets to the end and other things as well.

 

Since this is a new thread I'll mention again what I'm trying to do. I currently have a macro doing the above pretty well. It just has to toggle back and forth quite a bit. So I was thinking that it might be more efficient to copy as much as I can in excel and toggle over to the payroll program and type it in. Each cell would have to its own variable. I believe the info from Excel also has to have the formatting trimmed out before you type it out.

 

The info has to land in the right spots so I was thinking I could start with T1 and increment up from there, then the same when it is being typed out in the Pay Roll Program.

 

Here is what the excel spreadsheet looks like. The yellow area plus the rate area which is one column to the right is the info I transfer to the other program.

 

Edit: I should also mention I have to enter in the P/R Program one line at a time, in other words "Code", "Hours", "Field", "Phaze", "Rate". I can't do columns.

 

 

If I've understood you correctly, this is actually a lot easier than it first appears.

 

First highlight the info you want to process (the whole thing from the top-most left "1" to the bottom-right "9.50"). When

it is all highlighted run a macro that looks like this:

 

Clipboard Copy
Clipboard Save Text: "cartwheels3.txt"
ASCII File Begin Process: "cartwheels3.txt" (Tab Delimited Text )
 Text Box Display: Line Contents
ASCII File End Process
Delete File or Files: "cartwheels3.txt"

 

<CLIPC><CLIPST2:0:C:\Users\Steven\Documents\Macros\Macro Test Files\cartwheels3.txt><ADFBEG:F11:001:000001:000000:C:\Users\Steven\Documents\Macros\Macro Test Files\cartwheels3.txt><TBOX4:T:1:CenterCenter000278000200:000:Line ContentsCode: %T1%
Hours: %T2%
Field:  %T3%
Phaze: %T4%
Rate: %T5%><ADFEND><DOFILE:08:NN:C:\Users\Steven\Documents\Macros\Macro Test Files\cartwheels3.txt>>

 

For this test I opened a blank spreadsheet and copied column for column from the image you provided from A8 through

A32 over to E9 to E32, so my test used your exact information as provided for greatest accuracy.

 

I then highlighted the block of text in my spreadsheet and ran the above macro. My results were consistent. Where I

have a Text Box Display in my macro is where you would have the macro activate your other program and using

whatever technique is best suited to that program (Window Controls, Tabs, or - heaven forbid - Mouse Clicks on the

appropriate fields, etc.) the macro can input the required information wherever it belongs.

 

The first two command lines create a text file that the macro will use to set the contents of the various cells to Text

Variables. The final Delete File command gets rid of the file created at the beginning of the macro to help keep

your hard drive clutter-free.

[Cartwheels]

Wow, this has taken a much different direction then I was originally thinking!

I really think this is going to work and have been playing with it for awhile now. I able to get the info into the text file. I'm not deleting it for now so I can see that it is all there, but the text box displays are blank while processing. I have it text typing back into excel for now and I'm just getting blanks. I think I must be doing something wrong.

I'm very close.

 

Activate Window: "Microsoft Excel - Book1"
Delay 1 Seconds
Text Type: <ESC>
Delay 1 Seconds
Text Type: <CTRLD>g<CTRLU>A8:E26<ENTER>
Clipboard Copy
Clipboard Save Text: "testbook1.txt"
Delay 2 Seconds
ASCII File Begin Process: "testbook1.txt" (Tab Delimited Text )
 Text Box Display: Line contents
ASCII File End Process
Activate Window: "Microsoft Excel - Book1"
Delay 2 Seconds
Text Type: <ESC>
Text Type: <CTRLD>g<CTRLU>G8<ENTER>
Text Type: %T1%<ARROW RIGHT>
Text Type: %T2%<ARROW RIGHT>
Text Type: %T3%<ARROW RIGHT>
Text Type: %T4%<ARROW RIGHT>
Text Type: %T5%

[Cartwheels]

Ok I figured out my error. I needed to move ASCII File End Process to the bottom. Now it great. This very Interesting. I'm going to have to play with it a little now. Is there a way to stop the process if T whatever = 9999, for example or change gears if it comes across a certain number?

 

Also where I have Text Type T%1% ect at the bottom is there a way to have that increment up +1 without having to build a macro that has T1 to T99?

 

I'll have to say I'm really excited about this!

 

 

Edit: I answerd my own questions. I only need to go T1 to T5 then loops back to T1 again which is absolutly perfect. I will easily be able do all kinds of stuff with it from here.

 

Steve, and every one, a big THANK YOU! You helped me a ton!

[terrypin]

Did you scroll all the way to the right? Personally I love these forums, but sometimes the interface is a pain in the butt.

I hate having to scroll off to the right and back again just to read a post. For my posts, I take the extra step of entering

carriage returns on each line so that my own text doesn't go off into the far-right distance...

 

But still, even then, the Edit/Reply/Quote buttons are clear over at the end.

 

Duh! Thanks Steve, that was the explanation.

 

Seems surprising IMO that forums aren't smart enough to wrap text in these cases.

 

--

Terry, East Grinstead, UK

[Cartwheels]

This is working great and it so much faster then the method I was using before. It is amazing me how much better this is going to work!

 

I have one instance I am going to need help with. I will be using if variables for T1.

Most the time when the info gets entered into Payroll it will be:

T1 enter T2 enter T3 enter T4 enter T5 enter.

But if T1 is 60 for example it will be entered into Payroll this way:

T1 enter T2 enter enter enter enter. At this point I need to get rid of T3-T5. If I don't they will screw up the sequence and get enered on the next line as T1-T3. Is there a way to skip or get rid of those so that the sequence stays right.

[stevecasper]

This is working great and it so much faster then the method I was using before. It is amazing me how much better this is going to work!

 

I have one instance I am going to need help with. I will be using if variables for T1.

Most the time when the info gets entered into Payroll it will be:

T1 enter T2 enter T3 enter T4 enter T5 enter.

But if T1 is 60 for example it will be entered into Payroll this way:

T1 enter T2 enter enter enter enter. At this point I need to get rid of T3-T5. If I don't they will screw up the sequence and get enered on the next line as T1-T3. Is there a way to skip or get rid of those so that the sequence stays right.

This is really just a matter of using an IF/Else command sequence. Because each time the process starts on a new

line it also starts over on T1, there is no need to get rid of %T3%-%T5%, you simply don't use them within the If/Else

clause where they need to be omitted.

 

Something like this:

ASCII File Begin Process: "cartwheels3.txt" (Tab Delimited Text )
 If Variable %T1% <> "60"
Text Box Display: If T1 <> 60 Then Do This
 Else
Text Box Display: If T1 = 60 Do This
 End If
ASCII File End Process

<ADFBEG:F11:001:000001:000000:C:\Users\Steven\Documents\Macros\Macro Test Files\cartwheels3.txt><IFVAR2:1:01:2:60><TBOX4:T:1:CenterCenter000439000133:000:If T1 <> 60 Then Do This%T1%<ENTER>%T2%<ENTER>%T3%<ENTER>%T4%<ENTER>%T5%<ENTER>><ELSE><TBOX4:T:1:CenterCenter000351000133:000:If T1 = 60 Do This%T1%<ENTER>%T2%<ENTER><ENTER><ENTER><ENTER>><ENDIF><ADFEND>

 

Of course, if you are very concerned about it, you can simply set T3, T4, and T5 to "" within the appropriate IF/Else portion

of the command sequence, a la:

  If Variable %T1% <> "60"
Text Box Display: If T1 <> 60 Then Do This
 Else
Variable Set String %T3% ""
Variable Set String %T4% ""
Variable Set String %T5% ""
Text Box Display: If T1 = 60 Do This
 End If

<IFVAR2:1:01:2:60><TBOX4:T:1:CenterCenter000439000133:000:If T1 <> 60 Then Do This%T1%<ENTER>%T2%<ENTER>%T3%<ENTER>%T4%<ENTER>%T5%<ENTER>><ELSE><TVAR2:03:01:><TVAR2:04:01:><TVAR2:05:01:><TBOX4:T:1:CenterCenter000351000133:000:If T1 = 60 Do This%T1%<ENTER>%T2%<ENTER><ENTER><ENTER><ENTER>><ENDIF>

But this is really just unnecessary clutter.

[Cartwheels]

You know your right, for some reason I was thinking that once they were loaded I would have to do something with them, but if I don't use the them the will be renewed with the info for the next line. They will not screw up the sequence. So one less thing to worry about. It just keeps getting better.