Macro Timing

[Amerifax-Bob]

Problem is speed. Each time the macro runs if I can save 2-3 seconds it could amount to hours in a 24 hour day. The macro has apx 80 total lines of which 40-50 are a problem and these are the lines that do the data capture. The total macro takes 11 seconds to run. To capture 7 lines and turn them into variables it takes aproximately 9 seconds. It is capturing these 7 lines that I am trying to change from, right now it takes 9 seconds and I'd like to shave a couple seconds off of that.

 

Basically what we are doing is pasting into a program called Ultra Edit, doing a control home, shift end and turn the value into a variable. We arrow down to the next line do another shift end and create another variable. And we do this for the next 5 lines of text. We are trying to find out if there is a better way to do that.

 

Ideally it'd be nice if I could do my copy of the block of text and have the program see all the lines while it's in the buffer and create all my variables. Of course that would be similar to trying to play an MP3 file on your computer before you turn it on but actually we are looking for something that might be in between, just something better than shift end, down arrow...

 

Bob

[Cory]

I’ve suggested this many times before to you for exactly thisreason so I’ll try one more time to get thru to you then I’m giving up.

 

 

Load the file contents into variables directly and process them withthe variable manipulation commands. I gave up long ago doing things the visibleway and it’s rare I ever do it this way anymore. Even Excel spreadsheets I grabthe data without even opening Excel and process. I can tell you for a fact Ihave macros that run in couple ofminutes that would take days to run using your method. I know because I used todo them that way.

 

[Amerifax-Bob]

Cory it might seem like i'm not lisening but... There are days when we need to put "Cory" on the front door. We do thamk you for your great help but some times it is a bit over our heads.

Example:

variable manipulation commands

visibleway

 

I guess this was in the back of my mind when I said process on clipboard - Out ragious statement but only tring to make my point. I agree visual is killing the time when sharing only a couple of seconds amounts to hours each days. Right know I'm running 7 computers to process the date.

 

But you have a skill, knowlege, that slips by me. I'm not using "variable manipulation commands" becouse I have a better way. I know this is what I need to learn. Again don't think for a second that you have not infuenced our operations.

 

Bob

[kevin]

Where does the information exist before you paste it into UltraEdit?

[Amerifax-Bob]

Where does the information exist before you paste it into UltraEdit?

 

Sorry somehow posted my message a few times. Just doing this to take out the extra space...sorry!

Edited April 28, 2011 by Amerifax-Bob

[Amerifax-Bob]

Sorry somehow posted my message a few times. Just doing this to take out the extra space...sorry!

Edited April 28, 2011 by Amerifax-Bob

[Amerifax-Bob]

Sorry somehow posted my message a few times. Just doing this to take out the extra space...sorry!

Edited April 28, 2011 by Amerifax-Bob

[Amerifax-Bob]

Where does the information exist before you paste it into UltraEdit?

 

It a Phone Directory. Using "Alt vmo" ids the only way to capture the date. Some of the data only exist on other windows within Directory Program. From here I past "Ctr V" into any text program. I use MultiEdit. The format is almost always on the money. Some times the "Phone" is missing. I only use Name - Address - City ST Zip - Listed Since - Latitude - Longitude,

which I past into Excel-2007. This evening my biggest problem is closing the Vmo window so I can continue macro. "C

closes the window. So I do alt vmoc. I even tryied the "C" in the next line as TYPE TEXT.

 

// SelectPro - to Excel\T\File\Excel\SelectPro - to Excel (Waukesha Resident).xlsx

Repeat Start (Repeat 1 times)

Window Activate: ProCD PhoneBooks

Wait for Window Title: ProCD PhoneBooks

Text Type (Simulate Keystrokes): <ARROW DOWN><ALT>vmo

Also tried Text Type (Simulate Keystrokes): <ARROW DOWN><ALT>vmoc

Text Type (Simulate Keystrokes): c

Window Activate: UltraEdit

Wait for Window Title: UltraEdit

Text Type (Simulate Keystrokes): <CONTROL>a<DELETE>

 

 

Sample of Text:

HIRT KARI

W24081 CLOVER DR

SUSSEX, WI 53089

Phone: 262-372-4302

 

Listed Since: 01/2009

Residence

 

County: WAUKESHA (55133)

MSA: MILWAUKEE-WAUKESHA, WI (5080)

Latitude: 43.124640

Longitude: -88.226040

Precision: Geocoded to address precision

[kevin]

Once you have the information in the clipboard you can get it into a variable in a macro. Once in a variable, you can manipulate it using the various macro commands. Then you can type either it into Excel or save it in a file to be imported into Excel later. This would save a significant amount of time.

 

Pasting the information into Ultra Edit and then copying it again takes more time.

[Amerifax-Bob]

Once you have the information in the clipboard you can get it into a variable in a macro.

 

I only place it in UltraEdit to create the variables. Since my clipboard has the text as Example: I don't know how to create the variable, in the clouds, with out seeing it. I do know the line numbers without having to see it.

 

Bob

 

HIRT PETER

W299N7320 BREEZY CIR

HARTLAND, WI 53029-8478

Phone: 262-538-1186

 

Listed Since: 10/2003

Residence

 

County: WAUKESHA (55133)

MSA: MILWAUKEE-WAUKESHA, WI (5080)

Latitude: 43.150620

Longitude: -88.339320

Precision: Geocoded to address precision

[Amerifax-Bob]

Kevin - It just dawned on me that you must have been thinking I had only one line of text. I see that would be easy. It would be nice if we had a feature that would allow us to get items from clipboard by "Line Number". Here's hoping.

 

Bob

 

 

>>

HIRT PETER

W299N7320 BREEZY CIR

HARTLAND, WI 53029-8478

Phone: 262-538-1186

 

Listed Since: 10/2003

Residence

 

County: WAUKESHA (55133)

MSA: MILWAUKEE-WAUKESHA, WI (5080)

Latitude: 43.150620

Longitude: -88.339320

Precision: Geocoded to address precision<<

[Amerifax-Bob]

I might have an idea but I need someone to tell me if I'm going in the right direction. Example:

 

 

HAWKINSON Robert

5905 LANCASTER LN N

MINNEAPOLIS, MN 55441-1734

Phone: 763-544-2029

 

Listed Since: 06/2007

Residence

 

County: HENNEPIN (27053)

MSA: MINNEAPOLIS-ST PAUL, MN (5120)

Latitude: 45.088860

Longitude: -93.102420

Precision: Geocoded to address precision

 

This how I was doing it to the second line of text.

 

// Select: Address to Excel var2

Clipboard Empty

Text Type (Simulate Keystrokes): <ARROW DOWN><HOME><SHIFT><END>

Clipboard Copy

Text Type (Simulate Keystrokes): <ARROW DOWN><HOME><SHIFT><END>

Variable Set String %T[2]% from the clipboard contents

 

This is what I'm thinking.

"Shift End" "Delete" each line. Than repeat each line.

My problem is how to create variable.

The "Text Type" box gives me the "Shift End" "Delete" and also the mention of variable. I don't have a clue on if I can even capture at this point and get the variable i need. I was thinking if I created a globe variable for capture so I could use it in the "Text Type". I'm a summing all this since I saw variable listed in the "Text Type" box.

 

Bob

[kevin]

Simple:

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 %Lines%, starting at 1

// Here:
 %Lines[1]% contains line 1
 %Lines[2]% contains line 2
etc.

[kevin]

Here is another technique:

 
Variable Set String %Content% from the clipboard contents
Variable Modify String: Save %Content% to "d:\Contents.txt"
Text File Begin Process: d:\Contents.txt

// Here:
 The first time through the loop %OneLine% contains the first line.
 The second time through the loop %OneLine% contains the second line.
 etc.


Text File End Process

[Amerifax-Bob]

First off...It seems Cory was trying to show us this procedure and we just weren't getting it (*sorry*). So I would like to thank Cory and Kevin for all their help with this matter! That said...

 

I think I am finally starting to understand what you are trying to show here:

 

Variable Set to ASCII Char 13 to %CR%
Variable Set to ASCII Char 10 to %LF%

 

1st Line: Start with Character 13 and go to the "Return" and copy that into a Variable

2nd Line: Start with Character 10 and go to the "End of the Line" and copy that into a Variable

 

So would I be correct in thinking that both lines are actually showing the same thing, just 2 different ways to do it?

 

Variable Set String %Content% from the clipboard contents
Split String "%Content%" on "%CR%%LF%" into %Lines%, starting at 1

// Here:
 %Lines[1]% contains line 1
 %Lines[2]% contains line 2

 

Now this part is telling me...

1st line: Copy the contents of the clipboard to a Variable called "Content"

2nd line: Split the "Content" at the "Return" or "End of Line" into separate Variables called "Lines" starting with line 1. (Am I getting it???)

 

Then to get my result into Excel or whatever I would text type "%Lines[1]%" etc. So even if I have a blank line that would still count as a Variable, in my case %Lines[5]%, and I just would not use or paste it.

 

The second code you showed, I assumed that would process an entire file, if you had a long text file that you wanted to process? In my case my text will always be in the same format as below. After I copy it into variables and paste it, I copy the next record to the clipboard...

 

 

 

So once I do my Capture (AltVMOC), I have this in the clipboard (and it usually always, if not always, follows this format):

 

HAWKINSON Robert

5905 LANCASTER LN N

MINNEAPOLIS, MN 55441-1734

Phone: 763-544-2029

 

Listed Since: 06/2007

Residence

 

County: HENNEPIN (27053)

MSA: MINNEAPOLIS-ST PAUL, MN (5120)

Latitude: 45.088860

Longitude: -93.102420

Precision: Geocoded to address precision

 

I am going to give it a try.

 

Thanks again for all your help and patience!

 

Bob

[Amerifax-Bob]

OK I have been working with it and this is where I am now...

 

  Delay: 150 milliseconds
 Window Activate: ProCD PhoneBooks
 Wait for Window Title: ProCD PhoneBooks
 Text Type (Simulate Keystrokes): <ARROW DOWN><ALT>vmo
 Text Type (Simulate Keystrokes): c
 Variable Set String %Content% from the clipboard contents
 Split String "%Content%" on "%CR%" into %Line%, starting at 1

 Window Activate: UltraEdit
 Wait for Window Title: UltraEdit
 Text Type (Simulate Keystrokes): %Line[1]%

 

Result:

HESTAD JAMES

3905 LANCASTER LN N

MINNEAPOLIS, MN 55441-1734

Phone: 763-546-8059

Listed Since: 04/2005

Residence

County: HENNEPIN (27053)

MSA: MINNEAPOLIS-ST PAUL, MN (5120)

Latitude: 45.028860

Longitude: -93.402420

Precision: Geocoded to address precision

 

I am not sure what I am doing wrong (and I'm thinking it's minor). I think I might have the wrong Character in my Split On. I've tried %CR% and %LF% and neither one works for me. I am getting all the lines in variable %Line[1]%. In my text editor, it shows a "paragraph" mark at the end of each line, which I thought was a standard return or enter. I am almost there. And it is so much faster. I can't thank you enough for sticking it out and pushing me to listen more! I have been searching the help and I just can't get over this last hump...

 

Bob

[Amerifax-Bob]

I figured out that I was missing this:

 

 

  Variable Set to ASCII Char 13 to %CR%
 Variable Set to ASCII Char 10 to %LF%

 

I placed this in my Macro right above the "Variable Set String from Clipboard" but I am still getting the same response.

 

I am getting all the lines of text in %Line[1]%.

 

Bob

 

P.S. Where would I find those commands above "Char 13 and Char 10" it took me a minute to figure out and I remembered the same thing in one of Cory's messages and it finally hit me...

[kevin]

Different computers indicate the end of a line differently. To understand this a bit of history is in order.

 

Imagine the first printers. The first printers were similar to a typewriter, not like the ink jets and lasers of today. On a typewriter there is a key to move the paper up one line. There is also a key to move to the left of the paper. Eventually there was a lever to both move to the left side of the paper and move the paper up one line.

 

When the first printers (teletypes, actually) were created they were designed to support 'Carriage Return' (move to the left side) and 'Line Feed' (move up one line). This carried over to computers. CR means Carriage Return. LF means Line Feed.

 

On Windows based computers the end of a line is indicated by CRLF ... both a CR and a LF. On Unix and Linux computers the end of a line may be indicated by a LF by itself. And on other computers the CR may be interpreted as an end of line.

 

In the macro example I gave you I wrote that you should split on %CR%%LF% ... CRLF. If you split on CR you may have a line that contains

'This is the first line'

'<LF>This is the second line'

'<LF>This is the third line'

 

If you split on LF you would have something like this:

'This is the first line<CR>'

'This is the second line<CR>'

'This is the third line<CR>'

 

If you split on CRLF you get the proper result:

'This is the first line'

'This is the second line'

'This is the third line'

 

You cannot see the CR or LF characters, but they are there. And improperly handling of the CRLF will cause difficulties.

[Amerifax-Bob]

Thanks for the explanation. I understand now what they are and what they do and why both really are necessary. But...in my macro this is what I ended up doing:

 

  Variable Set to ASCII Char 13 to %CR%
 Variable Set to ASCII Char 10 to %LF%
 // Place Clipboard into Variable
 Variable Set String %Content% from the clipboard contents
 Split String "%Content%" on "%CR%%LF%" into %Line%, starting at 1

 

I am using both but I am still getting the full results in the %Line[1]% Variable. Is there something else or another character I can try?

 

Bob

[kevin]

It just dawned on me that you must have been thinking I had only one line of text.

Nope.

 

It would be nice if we had a feature that would allow us to get items from clipboard by "Line Number". Here's hoping.

No. There is no need to parse the clipboard. You can easily copy everything from the clipboard into a variable and parse the variable. Doing it this way is faster and it is already in place.

[Amerifax-Bob]

I did notice that I am not getting my blank lines...do they get their own variable or are they skipped within the code?

[kevin]

2nd line: Split the "Content" at the "Return" or "End of Line" into separate Variables called "Lines" starting with line 1.

Each line goes into an element of the array variable named 'Lines'. Lines is a single variable. An array variable.

 

A more accurate way to say it is:

 

2nd line: Split the "Content" at the "Return" or "End of Line" into separate elements of the variable called "Lines" starting with line 1.

[kevin]

You need to closely examine your data source to see if it uses CR, LF or CRLF to indicate the end of a line. The way I do this is to save it to a file and examine it with a Hex editor. There are free ones out there.

[Amerifax-Bob]

What exactly will a Hex Editor do for me and can you suggest one? I did try all the different combinations you suggested though. I tried, CR, LF and CRLF. Is there many more? Right now we use Ultra Edit which is a text editor and it shows a standard Paragraph mark at the end of each line. Does that count for anything?

 

Actually I just found that UltraEdit has a HexEditor as part of the program. I looked at it but I have no idea what it means. At the end of every line I see an "OD OA" what does that mean? I attached an image file that I was hoping maybe you could look at and tell me what it is that I need from it.

 

Actually I just found another part and it seems that it is using a CRLF. I have tried all these combinations...is there something I'm missing.

 

Thanks again,

 

Bob

 

This is the furthest we have ever gotten with this. We usually end up getting frustrated and put the project on the shelf again. With the new update of Macro Express we thought we would give it another shot but...

[kevin]

At the end of every line I see an "OD OA" what does that mean?

OD is the hexadecimal value of the number 13 and represents CR. You guessed it, 0A is the hexadecimal value for 10 and represents LF. Your lines are terminated with CRLF.