Amerifax-Bob

Any think close to a date in appearance should formate to date automatically. I tried 2-2-40 and it came up as a date 2/2/40. Also 5-23 will be a date. It's was typed in as 5-23. It shows 23-May in the cell and on the edit line it's 5/23/2011. I would think the date transfer would not be a problem. As far as the First cell doing a incremental increase from previous cell it's +a1+1 assuming you type it into b1. From here is is a ME from one sheet to the other. Delay is important when dealing with Excel. I have an extensive transfer of data in a previous topic, amerifax. Split City State and Zip Based on Comma http://pgmacros.invisionzone.com/index.php?showtopic=4908 I create variables of the text I'm adding to Excel. I'm told there is a better way which I'm open to but lack the knowledge. I'm here if more help needed. Bob

Disregard

I hope to help many with this instruction on how to deal with creating a variable based on this string form the ClipBoard: John Smith 477S 18TH ST OMAHA, NE 68107-2157 Phone: 402-346-6649 Listed Since: 08/2009 Residence County: DOUGLAS (31055) MSA: OMAHA, NE-IA (5920) Latitude: 41.214360 Longitude: -95.939340 Precision: Geocoded to address precision ------------------------------------------------------- Now it's payback time for all the great help I've gotten on this forum. I am going to detail start to finish how to take an address, such as city state zip and split apart and place it into Excel. Here , this is the code regarding the variable: MacroExpresss Pro V 2011.3.04.2300 1. This macro is based on captured text. The source that we are working with will not work with a simple capture. It first must be pasted into a text editor. Then recaptured before we can begin. This is a limitation created by the source program. 2. Assuming you have already captured your data. It's time to convert the clipboard to variables. Sample Code: // Place Clipboard into Variable Variable Set to ASCII Char 13 to %CR% Variable Set to ASCII Char 10 to %LF% Variable Set String %Capture% from the clipboard contents Split String "%Capture%" on "%CR%%LF%" into %Line%, starting at 1 Delay: 185 milliseconds Note: All values that are in between %???% are names that we give the variable a. "ASCII Char 13" - System term for carriage return, not a ME term. It's a system term for carriage return We gave it our name "CR" b. "ASCII Char 10" - System term for "Line Feed" or End of Lin c. "Variable Set String" Create a variable called "Capture" using clipboard contents. See Picture called c-Variable Set String This will take the text on the clipboard and split it into lines. You can see the results by going into the Menu "Debug - Show Variable Values." I many times asked if there was a way to see the clipboard, what I called the clouds. Well here it is... C - Variable Set String d. Split String (setup %Line% Variables by splitting at %CR%%LF%) So setup a variable for each line in the clipboard. e. Split String to seperate the City, State and Zip Code First I split the line at the ", " So that the City is in variable %CitySt[1]% and the State Zip are in Variable %CitySt[2]%. f. Split String to Seperate State and Zip Code Here I split the Variable %CitySt[2]% into 2 variables, at the double space, one for state and one for zip (%StateZip%). g. Then to remove the headings from from my last few Variables such as (Phone: 262-222-2222), I used: Variable Modify String Now it's time to place a variables into Excel. I feel this is pretty self-explanatory and needs no explanation. But don't hesitate to ask questions. If you need interpretation. Delay: 185 milliseconds Window Activate: Microsoft Excel - Book1 Wait for Window Title: Microsoft Excel - Book1 Delay: 185 milliseconds Text Type (Simulate Keystrokes): %Line[1]% Text Type (Simulate Keystrokes): <ARROW DOWN>%Line[2]% Text Type (Simulate Keystrokes): <ARROW DOWN>%CitySt[1]% Text Type (Simulate Keystrokes): <ARROW DOWN>%StateZip[1]% Text Type (Simulate Keystrokes): <ARROW DOWN>%StateZip[2]% Text Type (Simulate Keystrokes): <ARROW DOWN>%Line[4]% Here is the complete code used once the data is on the clipboard: // Place Clipboard into Variable Variable Set to ASCII Char 13 to %CR% Variable Set to ASCII Char 10 to %LF% Variable Set String %Capture% from the clipboard contents Split String "%Capture%" on "%CR%%LF%" into %Line%, starting at 1 Delay: 185 milliseconds // Split Variable for City, State Zip Split String "%Line[3]%" on ", " into %CitySt%, starting at 1 Split String "%CitySt[2]%" on " " into %StateZip%, starting at 1 Variable Modify String %Line[4]%: Delete a substring starting at 1 and 7 characters long Variable Modify String %Line[6]%: Delete a substring starting at 1 and 14 characters long Variable Modify String %Line[11]%: Delete a substring starting at 1 and 10 characters long Variable Modify String %Line[12]%: Delete a substring starting at 1 and 11 characters long // Place values from SelectPhone into Excel Delay: 185 milliseconds Window Activate: Microsoft Excel - Book1 Wait for Window Title: Microsoft Excel - Book1 Delay: 185 milliseconds Text Type (Simulate Keystrokes): %Line[1]% Text Type (Simulate Keystrokes): <ARROW DOWN>%Line[2]% Text Type (Simulate Keystrokes): <ARROW DOWN>%CitySt[1]% Text Type (Simulate Keystrokes): <ARROW DOWN>%StateZip[1]% Text Type (Simulate Keystrokes): <ARROW DOWN>%StateZip[2]% Text Type (Simulate Keystrokes): <ARROW DOWN>%Line[4]% Text Type (Simulate Keystrokes): <ARROW DOWN>%Line[6]% Text Type (Simulate Keystrokes): <ARROW DOWN>%Line[11]% Text Type (Simulate Keystrokes): <ARROW DOWN>%Line[12]%<ENTER> I hope I have helped. All the above is much research with the biggest dictionary being members of this forum. Most notably Kevin and Cory. Bob

I went ahead and put in the delay on every command, which kinda makes me feel like I'm taking a bucket of delays and throwing it at the program but I don't really know what I'm doing. I don't know how many times I've read the delay instructions. But we still don't understand exactly how it works as far as, well to me it seems like the delay is supposed to work just for the one command. We tried to find a place that would show us how many milliseconds the program is running at, true every machine is different. If I at some point suspect a timing issue and I use a delay at 0 would that be placing the system back to normal speed? Any help in this matter would be useful although I've made a fix in the macro by throwing delays everywhere. We're also curious if we "red line" items so they don't play, can that effect the speed of the macro? Do you know many years ago a programmer said to me, "Bob I don't care how many people check out what I work on, but when I give you a program that's complete, you'll break it every time." I just seem to have a knack with unfortunately, finding things that don't work than that do work. This I apologize for but I feel that many people can benefit that have similar problems that I run into I just seem to find them all at once. Believe me when I tell you Dyslexia does not make it easy when it comes to directions. But I fight the battle...In case your not aware what it is, when normal people read something they interpret it the same as most others, with dyslexia they read and interpret in a mixed up fashion, and some have it worse than others. I am close to normal if there is such a thing, and for the most part can work through the instructions. I mention this because you seemed a little put out in the last response and I totally don't blame you for being put out, cause you have the same problems I have, only in reverse, trying to deal with people that just don't seem to get it. Any-who...Have a great day! Bob

>>Set the value(s) high, test the macro(s) and then lower the values. << We do use delays but I suspect not effectively. My high would you say 350 ms. Also if I understand delay rightat the point of the command delay time is changed. But after the next command runs. It seems to me that timing is back to normal. If that is not true. Then I would need a way to bring it back to normal and I have attempted to test using delay set to zero. Am I right in my assumptions that delay is only a temporary command by command delay.

It's a couple of minutes since my last post. You got it (it's not working). This is driving me nuts. Nap time!!!

I finally got it to work. No that's a lie. A little elf came in the middle of the night and fix my computer. I don't know what he changed, but I tried the same code last night many times over what no results. I might mention that the macro I created an hour prior to this problem was working great. And when I ran into these problems. I tested it to find it wasn't working now. I know all and 80 can be temperamental. Or maybe it's me. Last night this same code through up on me and today it works fine. Please someone tell me,I'm begging, what can create this problem. It does appear that it is most likely coming from the computer itself. But I had turned everything off except for the program I restarted many times over.

I did change back to: Text Type (Simulate Keystrokes): <ALT>vmoc But that's not the cure,

Thanks for help. I should have listed the macro, all. I do the capture just be for the code I had in the topic. Here is the macro as far aas I'm at.: Window Activate: ProCD PhoneBooks Wait for Window Title: ProCD PhoneBooks Delay: 185 milliseconds Text Type (Simulate Keystrokes): <ALTD>vmoc<ALTU> Window Activate: Document - WordPad Wait for Window Title: Document - WordPad Text Type (Simulate Keystrokes): <CONTROL>a Text Type (Simulate Keystrokes): <DELETE> Mouse Left Click Clipboard Paste As you see I do the capture just above the code you saw. Problem is that the capture can only be made using the source program. The capture is there, that's not the problem. It stops at the point it should past in the WordPad window. I know it sees WordPad because the top bar turns from gray to bright blue, the affect when you active a window. Also when macro stops I then click in the window it places the text into the WordPad window. So it is stops just before placement. I have tried delays of a sorts. I have even tried mouse move. I have being using ME for years and It's is always at this point,timing, that I put it back on the self. But this time that's not an option since It would effect me business greatly. Know I'm at a point mind or matter. Yes it does matter unfortunately more than mind.

Here I set with a smallclip that just defies the laws of gravity. Window Activate: Document - WordPad Wait for Window Title: Document - WordPad Text Type (Simulate Keystrokes): <CONTROL>a<DELETE> Mouse Left Click Clipboard Paste There is more to it but this is the basic clip. I have a capture that from a source I use all the time that will not work. The 3rd line should delete the text the 5th should do a past. It will not. But if I manually click in the wordpad window it will do the delete and past.I have tried every thing in the last couple of hours.

Weird idea. Can a globe be set? As a TextType or just part of the macro. Anything with <<???8>> would equate to the value 8 times or what ever the # is. The macro would see <<>> and know the condition.

The cure is a bit much but I will surly use this at times it would be helpful. It seems that the ME code would be able to handle such a request as <Arrow Right 8> or <Arrow Right>=8

Yeah I am trying to do just that but I am looking for the Phone number to be in the Variable, not the "Phone:" "Phone: is only stating the items that come before the value,#, that I need. "Phone: " is always at the same position and always the same text "Phone: "

I kind of figured that's what I Had to do. When it's in a line I can do the following (Thanks to you guy's). // Place Clipboard into Variable Variable Set to ASCII Char 13 to %CR% Variable Set to ASCII Char 10 to %LF% Variable Set String %Content% from the clipboard contents Split String "%Content%" on "%CR%%LF%" into %Line%, starting at 1 // Place values from SelectPhone into Excel Delay: 250 milliseconds Window Activate: Microsoft Excel Text Type (Simulate Keystrokes): %Line[1]% The problem is "Phone: ". I know it's there but I can't, so to speak, see it. I could save it to a edit program and know exactly what I need to do but that's defeating the purpose. It seems I'm trying to see what's out there on the clouds.

Some where on this forum I saw someone looking for a simple way to deal with <Delete> or and thing in TEXTTYPE with out having to write out <Delete> 8 tines. It seems like a natural for multipliable values. Example:Short Form: Text Type <Delete>=8 Example:Long Form: Text Type <Delete><Delete><Delete><Delete><Delete><Delete><Delete>

Phone: 262-222-2222 %Line% will give me whole line Phone: 262-222-2222 If this was in a text file I would do a control, shift, arrow, arrow, then arrow, arrow, shift, end to select just the phone number and add to variable. (Two Slow) Phone: 262-222-2222 But I'm on the clipboard - not text file From the clip board I want to copy only phone number to variable %Phone% Problem: Don't know how. Bob P.S. Please disregard prior message in forum.

Several Keys can be held down: <SHIFTD><TAB><TAB><TAB><TAB><SHIFTU> <CTRLD><SHIFTD><WIND> Not to forget to bring back up. <CTRLU><SHIFTU><WINU> Not sure if that's what you want.

The unknown. Always the biggest problem. Thanks for responding.

I probably use the basic interface for adding and modifying macros. Here if you use "Copying Command Text" you will be able to see the macro more user-friendly. by now I would think you have found a solution to your problem. It would be nice if you would share your solution with all. The moderator of this forum expects those that he helps to step forward in topics such as this. But also we all could benefit from a topic, which was important enough by one, when it's brought to a conclusion with a result.

If you're entering information on the website you have to type it in. could you not see and copy what you just typed then use "ME" to process the information from the clipboard. Not all owners of Excel have Access. But if you have Access would that not be a better solution. If you have not resolved your issue I would like to help. If you have resolved your issue would you be interested to bringing this topic to a conclusion by sharing with us all your solution.

this is an extremely old topic but it is an answer and here goes. we use any to process millions of records in regards to address. I'm sure I have a unique situation, not like your own, but we need to capture each record from the source and move it into an Excel database, which we now do. but it seems to me what you're trying to do is rather simple and that would be just to append your data as it comes in to Excel. By now I'm willing to bet you have already accomplished this. There is another issue that you might be presented with and that is comparing your data source to your master. you might be trying to verify the fact that record is not already in the master database or updating address phone or name information within each record. all this is very possible and most likely can be done within Excel controlled by ME macros. But access would be a more logical place to accomplish all of this. we have written some very heavy duty macros to process millions of records. I do have to mention none of those macros would be in place if it had not been for the help on this forum. You're being an advanced user is made the conclusion that I must be missing something and what you're trying to accomplish since it all seems so basic. No topic should ever have zero responses. I believe I now know why some responses are zero. Kevin see's what can be responded to by users, such as myself,and gives us he opportunity to be involved. More important is that a zero topic has also no interest to the poster of the topic. If the topic was important enoughto be posted on the forum issued be even more important to the poster to bring it to a proper conclusion. Please don't take this personal not meant to be. It's more of a confession by a user that is guilty all of proper conclusion to a topic. in the case of this topic I truly would like to know how you concluded your problem. It's as important to me as it was to you...

Can't be much of a help. Since your topic is very old I thought maybe I could inject something new to the issue. It almost sounds like the issue is coming from outside of macro express or memory. This responses is coming from a user a tad bit over "Newbie" status, possibly less experienced than you yourself. I would be curious to know what your problem was. Too many times I see topics with no responses but bigger than that no conclusions to the issue. I guess from experience the board moderator has an insight to how some topics will conclude.

I have responded to another message regarding screen resolution. Not sure what your problem is but screen resolution does appear to possibly be an issue. since you have had no responses to your topic in some time I thought I would throw this in. I have had no issues between 32 and 64 with "XP" or Windows 2007 regarding "ME". This response is from an unskilled user all"ME" Pro. Not trying to get down on myself just wanted you to know where the tip comes from.

I know this is a very old response to your problem but... in a previous topic I saw where the monitor resolution was changed and therefore stopping the execution of the macro. It was running well on most computers with the exception of one. And the issue was monitor resolution.

I'm trying to get a handle on what you are saying. a. You are trying to run a macro "A" that well execute step one, step two and step three. You press the escape key and it cycles through one more time. b. You have another macro "B" c. You have another macro "C". If macro "A" used a "repeat 1" why would you need "ESC"? Could you have a macro that would call "A", "B" or "C", by your choice. You would be able to execute "A" it then returns to call in macro. The calling macro amount gives you the same three choices "A", "B" and "C" where you could now select "B" or "C". I would hope to be classified as an average user all of Macro Express. That statement is a bit over-the-top. But what I'm trying to do here is logically understand what you are trying to do. But it seems like ME would have no problem accomplishing what you're trying to execute. Since you have no responses to your topic in the last couple of months I thought why not try to help. If you have solved your problem would you care to share it.