cyberchief

wondering if ME can do this... I need ME to extract the second row of multiple spreadsheets (96) and put this into a new Excel file. can excel do this without opening every single excel file? Or does it need to access every one? Problem I am having is that I am pulling up information in a system that is not very freindly... It has no feilds to copy from. The only thing I can do is save the data to excel. I have 96 accounts that it needs to pull... other problem is that it pulls aged information and I only need the most current (row 2). Any ideas?

Joe, Thanks!!! That worked PERFECT!

Currently, I have 6 macro files... all exactly the same... I use the File manipuation command to overwrite 5 of them using my main file (the one I make all changes in). This works fine. I also have some individual commands that when executed, will only overwrite 1 of the other files with my main file. Basically, I have 5 terminals set up with each running one of the macro files... terminal 1 is running macro1.mex, terminal 2 is running macro2.mex, etc. When I run a project, I either have to make the changes and input the variable information in each mex file (mulitple terminals running the same project)... or I have to make it in my main mex file, and run the overwrite... then erase the changes I made in the main mex file. What I would like to do... is be able to make the changes in any one of the mex files... and overwrite any of the others... sounds confusing... but I want to set a variable to equal the current mex file I am in... and in the file manipulation, set the file path "from" to %T1% (current mex file). Currently, this is what I have to overwrite just 1 other mex file: <DOFILE:05:NN:Z:\H_Drive\Gba\QMacro\Main Macro File\Main.mex>Z:\H_Drive\Gba\QMacro\Main Macro File\Macro 1.mex> This is what I would like to do with %T1% equaling the current mex file I am in whether it be 2, 3, 4, or 5 (not 1, since that is what the below example is changing): <DOFILE:05:NN:%T1%>Z:\H_Drive\Gba\QMacro\Main Macro File\Macro 1.mex> Is there a way to set %T1% to equal the mex file I am currently using? and will the variable work in the file manipulation?

Here is one idea... after setting the variable... modify to save that text to the clipboard and another command to paste it. Variable Set String %T1% "This is some text here... " Variable Modify String: Save %T1% to Clipboard Clipboard Paste <TVAR2:01:01:This is some text here... ><TMVAR2:16:01:00:000:000:><CLIPP>

can you post your code so we can see it?

Thanks Joe. Looks great. not quite sure I understand it completely... but I will look closer and figure it out. Appreciate the help!!!

Ok, this may be crude... but I think it will work. What I am attempting is to set all text up to the /PIC to variable T10. I already have N4 set to the position of /PIC. I have set N1 and N2 to the length of characters in that new varaible (should be the same as N4). I set a repeat loop starting from the end of that new copied text and copy 1 character. I set that character to T11, then append to T12. I then delete the last character of the string (the one that was just sent to T12) and repeat loop. The end result would be reversing the entire text string. Clipboard Copy Variable Set String %T10% from Clipboard Variable Set Integer %N1% from Length of Variable %T10% Variable Set Integer %N2% from Length of Variable %T10% Repeat with Variable using %N1% Variable Modify String: Copy Part of %T10% to %T11% Variable Modify String: Append %T11% to %T12% Variable Modify String: Delete Part of %T10% Variable Modify Integer: %N2% = %N2% - 1 Repeat End Text Type: %T12% <CLIPC><TVAR2:10:03:><IVAR2:01:12:10><IVAR2:02:12:10><REP3:05:000001:000001:0001:0:01:><TMVAR2:10:11:10:N02:001:><TMVAR2:08:12:11:000:000:><TMVAR2:11:10:00:N02:001:><NMVAR:02:02:1:0000002:2:0000001><ENDREP><TEXTTYPE:%T12%> So this text "The red fox jumps over the lazy dog" would be converted to "god yzal eht revo spmuj xof der ehT". What I will do next is, get the position of NT/. Set that to N5. I then subtract N5 from N4, and that should give me the position of /TN in the main string variable. Probably have some tweaking to do... but I think this may work. Any suggestions or critisisms?

Hey Joe. I have it on 1 machine.. but honestly, I haven't had any use for it. This could change in the future... but as of now, I can't get the company to pay for it if I do not have an immediate and drastic need for it. Also, fyi... we are finally setting up a lab... so we will be ordering probably about 5 or so more licsense.

Ok, I'm stumped on this one. I need to find the position of specific text but not from the beginning of the string variable. In the below example, I need to copy the /TN and Data just prior to the /PIC text... not the first /TN, but the second one. Basically, I need to find /PIC and then find the /TN that is just before it. The position of the /TN can vary before the /PIC text. So I cannot just subtract a specific number off the interger. I need to find /PIC, then find the first previous /TN. text text text /TN 111 111-1111 text text text text text /TN 222 222-2222 text text text text /PIC text text text. End result, I need to copy /TN 222 222-2222 to a seperate variable. I am sure there is a way to do this... but it is escaping me somehow. Any Guidance?

Bump... oops... Did I do that? Anybody else have successes they would like to share? I, for one, would like to hear what others are doing. Gives me ideas.

Well, I came into ME back in March of this year. And I do have to say that it has been the best thing to happen in my life since the Vikings won the superbowl. Well, Ok, so they haven't won yet... but hey, if ME can automate just about every project that comes by my desk, why can't it automate Culpepper to keep him from throwing interceptions?!?! Ok, enough with that. I work for a large phone company in the large business division. Everything we touch is LARGE. We are basically the front line to the customer. When our customers call in for changes... it is usually on thousands of accounts. Not just one or two. I have built about 60 Macros to automate common requests... billing address changes, payments, audits, service change, etc. Although I have only built 60 working... these are built to be as flexible as possible. Formatting and such changes with every customer, so I have built them to be simple enought to set 4 or 5 varaibles at the beginning, and the macro takes it from there. I have to say that my most recent success was building a macro to correct orders that were written incorrectly but a team of "proffessionals". There were about 6000 orders written effecting 2 state government customers. It would have taken a team of 6 individuals 2 months of full time work to correct these. With about 6 hours of build time, my macro corrected and completed these orders in about 14 hours. Was quite the hit with upper management. I am only doing this Macro stuff part time right now... we are working on creating a "promotion" for me to do this full time. Crunching the numbers... we expect that, working full time, this program will save our company $1.5 Million a year.

Here is an addition to the code I posted to check for symbols... you can modify as needed... this is only an example. Variable Set String %T1% from Prompt // Here is where the macro checks the length and prompts if too long Repeat Until %T1% <> %T1% Variable Set Integer %N1% from Length of Variable %T1% If Variable %N1% < 10 Break Else Variable Set String %T1% from Prompt End If // Here is where the macro checks for characters/Symbols Variable Set String %T3% "!~@~#~$~%^~&~*~(~)~*~{~}~|~:~"~<~>~?~[~]~\~;~'~,~.~/~-~+~`" Repeat Start (Repeat 10 times) Variable Modify String: Copy Part of %T1% to %T2% If Variable %T3% contains variable %T2% Variable Set String %T1% from Prompt Break End If <TVAR2:01:02:FEnter Value HereFFCenter:Center><REM2:><REM2:Here is where the macro checks the length and prompts if too long><REP3:08:000002:000001:0001:1:01:T1><IVAR2:01:12:1><IFVAR2:2:01:3:10><BREAK><ELSE><TVAR2:01:02:FCharacters Exceed 10 Count; Please Re-EnterFFCenter:Center><ENDIF><REM2:><REM2:Here is where the macro checks for characters/Symbols><TVAR2:03:01:!~@~#~$~%^~&~*~(~)~*~{~}~|~:~"~<~>~?~[~]~\~;~'~,~.~/~-~+~`><REP3:01:000001:000001:00010:0:01:><TMVAR2:10:02:01:001:001:><IFVAR2:4:03:7:T2><TVAR2:01:02:FPlease enter 10 digits excluding special charactersFFCenter:Center><BREAK><ENDIF>

How about this? Variable Set String %T1% from Prompt Repeat Until %T1% <> %T1% Variable Set Integer %N1% from Length of Variable %T1% If Variable %N1% < 10 Break Else Variable Set String %T1% from Prompt End If <TVAR2:01:02:FEnter Value HereFFCenter:Center><REP3:08:000002:000001:0001:1:01:T1><IVAR2:01:12:1><IFVAR2:2:01:3:10><BREAK><ELSE><TVAR2:01:02:FCharacters Exceed 10 Count; Please Re-EnterFFCenter:Center><ENDIF>

Sorry, Forgot to add: THANK YOU JOE AND RANDALL!!! Your help is greatly appreciated!!!

Ok... I think I may have got it. I did a Set String %T30% to "" in the middle of the loop (after it sets to %T1%). That seems to have made it work. For some reason, even though I was updating %T30% it wasn't overwriting it.

Ok... it is kinda long.. but here it goes. The macro I have is to do an audit of taxes billed on monthly invoices. This portion pulls up the invoice, copies each page until it hits the tax page. because the taxes can be on mulitple pages, it copies each page until it gets to the "Federal Universal" portion which is always at the end. It breaks there. With each page copy, it sets it to T31 and amends that to T30... so at the end of that loop, I have about 3 or 4 pages set in T30. I am trying to set this value to the variable... because after that, I have to go back 1 month and do the same thing with the prior months invoice. (up to 3 years back) <REM2:Start Loop (36 month)><IVAR2:09:01:50><REP3:05:000001:000001:0050:0:01:><REM2:><REM2:Find FEDERAL EXCISE><IVAR2:40:01:1><REP3:08:000002:000001:0001:1:01:T1><TEXTTYPE:<CTRLD>a<CTRLU>><CLIPC><TVAR2:31:03:><TMVAR2:08:30:31:000:000:><IFOTH2:08:1:FEDERAL UNIVERSAL><BREAK><ELSE><IFVAR2:2:40:4:10><BREAK><ELSE><NMVAR:01:40:1:0000040:2:0000001><TEXTTYPE:<F11>><CAPCONTROL:T:3:1:000449,000703><REP3:08:000007:000001:0025:0:01:Logged in><VARGETCONT:1:25><IFVAR2:1:25:7:Logged in><BREAK><ENDIF><ENDREP><ENDIF><ENDIF><ENDREP><TMVAR2:04:30:00:000:000:><TVAR2:01:01:<TMVAR2:09:%N9%:30:000:000:>><RUNMACVAR:1><REM2:><REM2:Retreive next bill><REM2:><TEXTTYPE:<F6>><CAPCONTROL:T:3:1:000449,000703><REP3:08:000007:000001:0025:0:01:Logged in><VARGETCONT:1:25><IFVAR2:1:25:7:Logged in><BREAK><ENDIF><ENDREP><MSD:100><TEXTTYPE:<CTRLD>a<CTRLU>><CLIPC><TVAR2:02:03:><IFVAR2:1:02:7:REQUESTED><BREAK><ENDIF><NMVAR:01:09:1:0000009:2:0000001><REM2:END of LOOP (36 month)><ENDREP><MACRUN2:Bill TAX Note Spreadsheet> The next portion here takes those values and finds what I am looking for.. mainly Federal, State, County, and City tax. It should pull the very first value of T30 and search that for all the info. It goes to the spreadsheet and notes it. Loops back and should go to the next page that I set in the variable. Here is where I am having the problems.... it is not incrementing N9 to the next page. <NMVAR:07:09:0:0000096:0:0000000><NMVAR:02:96:1:0000096:2:0000050><ACTIVATE2:%T99%><IVAR2:09:01:50><DELAY:1><REM2:><REP3:05:000001:000001:0096:0:01:><TVAR2:30:01:><TVAR2:32:01:><TVAR2:34:01:><TVAR2:36:01:><TVAR2:38:01:><REM2:><REM2:Set T30 from %T%N9%%><TVAR2:01:01:<TMVAR2:09:30:%N9%:000:000:>><RUNMACVAR:1><REM2:><REM2:><REM2:Get Tax Info><REM2:><MACRUN2:Bill Tax Get Amounts by Invoice><REM2:><REM2:><REM2:Enter Federal Tax Info><MSD:100><TEXTTYPE:%T32%><MSD:100><TEXTTYPE:<ARROW DOWN>><REM2:><REM2:><REM2:Enter State Tax Info><MSD:100><TEXTTYPE:%T34%><MSD:100><TEXTTYPE:<ARROW DOWN>><REM2:><REM2:><REM2:Enter County Info><MSD:100><TEXTTYPE:%T36%><MSD:100><TEXTTYPE:<ARROW DOWN>><REM2:><REM2:><REM2:Enter City info><MSD:100><TEXTTYPE:%T38%><MSD:100><TEXTTYPE:<ARROW RIGHT>><MSD:100><TEXTTYPE:<ARROW UP><ARROW UP><ARROW UP>><MSD:100><REM2:><REM2:><NMVAR:01:09:1:0000009:2:0000001><REM2:><ENDREP><TEXTTYPE:<CTRLD><ARROW LEFT><ARROW LEFT><ARROW LEFT><CTRLU>><MSD:100><TEXTTYPE:<ARROW DOWN><ARROW DOWN><ARROW DOWN><ARROW DOWN>><MSD:100> Inside that, I run macro Bill Tax get Amounts by invoice. Here is the code for that: <REM2:Get Federal><IFVAR2:1:30:7:FEDERAL EXCISE><IVAR2:76:13:30:FEDERAL EXCISE><NMVAR:01:76:1:0000076:2:0000044><TMVAR2:10:32:30:N76:010:><ELSE><TVAR2:32:01:XMPT OR 0><ENDIF><REM2:><REM2:Get State><IFVAR2:1:30:7:STATE SALES><IVAR2:76:13:30:STATE SALES><NMVAR:01:76:1:0000076:2:0000044><TMVAR2:10:34:30:N76:010:><ELSE><TVAR2:34:01:XMPT OR 0><ENDIF><REM2:><REM2:Get County><IFVAR2:1:30:7:COUNTY SALES><IVAR2:76:13:30:COUNTY SALES><NMVAR:01:76:1:0000076:2:0000044><TMVAR2:10:36:30:N76:010:><ELSE><TVAR2:36:01:XMPT OR 0><ENDIF><REM2:><REM2:Get Local><IFVAR2:1:30:7:CITY SALES><IVAR2:76:13:30:CITY SALES><NMVAR:01:76:1:0000076:2:0000044><TMVAR2:10:38:30:N76:010:><ELSE><TVAR2:38:01:XMPT OR 0><ENDIF><REM2:>

Hey Joe! I am not too concerned about whether it starts at T51 or T50. It will loop a maximum of 36 times... but not always. I have reset T30 in the loop that goes bac to process the data... but I am hitting a snag. I have bothered you all enough so I will try and see what I can figure out with this. During the first loop, it outputs the info correctly... but I modify N9 by incrementing up by 1 (as I do in the first loop), but it inputs the same data as the first loop (like it is not incrementing). Very strange. Anyway, i will keep working with it. Thanks much!

Thank you very much Randall and Joe. Sorry for being such a pain. For some reason.. the logic on this stuff is escaping me... I have put Joe's code in... and so far so good... one little problem. When I go to retreive the info after increasing N9 by 1, it does not seem to be pulling the info for the new month. I will continue to work with it... I think I am close. Thank you all for all the help!!! It is really appreciated.

Randall, In your textbox message... you are calling %T10%... not the %T%N9%% variable.... since you haven't changed %T10%... it will always say "Hi". When changing the textbox to say %T31%... it produces "<TMVAR2:09:10:50:000:000:>" not "Hi". hmm....

Ok. Back to the %T%N1%%. Here is what I am trying to do: I set %N9% to a value of 50. I set a repeat loop (up to 36 times). Screen copy a page out of the program, set to T31, amend T31 to T30, page back and screen copy again (in another loop), set to T31, amend to T30. So, I have 2 screen copies set to T30 now. I need to set T30 to a %T%N9%% so I can go back a month in the program, increment %N9% up by 1, and do the above again. Again, this can happen up to 36 times. All of my screen copies will then be set to %T50% through up to %T86%. I need to set %N9% back to 50. Set a loop that will set %T30% from %T%N9%%. I need to process some text values depending on the contents of %T30%, then loop back and do this again after incrementing %N9% by 1 (so I will be working with the next month's screen copies). To make this easier, can someone post an example of the following? %N9% set to 50 Repeat loop twice, clipboard copy a text set text to variable, then set to %T%N9%% modify %N9% + 1 Repeat end %N9% set to 50 Repeat loop twice Set %T10% to equatl %T%N9%% TextType %T10% Modify %N9% + 1 Repeat end If someone can show me how to format the above, I may be able to figure it out. My macro gets kinda complicated with other commands... I am just trying to figure out this portion. All the examples I see are only setting %T%N1%% once... except for the macro example that Kevin's link goes to... haven't been able to figure out how that one works yet...

Kevin, If the 32K is for ALL enviroment variables together, then this will not work for my purposes... Just one varaible set is about 5K. I am using XP. But what is interesting is that I do not have any enviroment variables set (that I know of)... and just the 5K one will not work. Guess I am back to trying to figure out how to work with %T%N1%%.

Ok, I have been able to determine that the Enviroment variable cannot handle so many characters. It is not the display box... as I changed this to cut from a text pad, set to varaible from clipboard, set to enviroment variable, set string from enviroment variable, and type into the text document. Large amounts of characters causes it to not go to the enviroment variable. When I cut down the data... it goes through fine. I have been using MS Word to try and determine the character limit... but it seems to change depending on the CRLF's and such.

That's got to be it. There must be some sort of character limit. I cut out the portion of code I am working on and put it into another blank macro. I modified as below: Text Type: <CTRLD>a<CTRLU> Clipboard Copy Variable Set String %T30% from Clipboard Variable Modify String: Save %T30% to Environment Variable Variable Set String %T30% "None" Variable Set String %T30% from Environment Variable Text Box Display: Test <TEXTTYPE:<CTRLD>a<CTRLU>><CLIPC><TVAR2:30:03:><TMVAR2:19:30:00:000:000:Tax(%N50%)><TVAR2:30:01:None><TVAR2:30:11:Tax(%N50%)><TBOX4:T:1:CenterCenter000278000200:000:Test%T30%> Now, I used a large text file... and ran it on there. The text box would not display anything. When I cut that text file down to all but 2 words... it worked perfectly. Hmm... or maybe it is an issue with the text box display?

Randall, I wonder... Is there a limit to how many characters (bytes) can be set to an enviroment variable?

Nope... that was completely wrong. Anyone have any ideas per my code above?