Jump to content
Macro Express Forums

If it was easy we would not need Macro Express


Recommended Posts

I have been out for two weeks and left Heather a job that I could not even do. I thought it would be best to try to summarize her efforts. The following are her messages she used for help.

 

1. Erratic Copy Command

2. Variables in Multiple Macros

3. Diff Macros Dependent on one location

 

This is what I'm trying to do:

1. http://www.mbaonline.org/MBAGeoLocator/Disclaimer.aspx

a. Click - I accept

b. Use tab "Find a Lot"

c. First box - Search All

d. Click - Search

This will open the Explore window that is always loaded and open when macro is run.

 

2. We have to click on the "More Details". Each time we run the macro we have to execute the next "More Details".

3. The "More Details" opens a window that we copy all the info into variables.

4. We then paste the data into Excel, which works good with the Act! program that will receive the data. The macro we are trying to develop does not have to be involved with getting the data to Act!

 

Copying and pasting the info works OK but is painfully slow. We do have two issues with our macro besides being slow.

 

The macro starts with the second window http://www.mbaonline.org/MBAGeoLocator/Lot....aspx?recID=500 being open.

The reason is because we use several versions of the same macro. They are dependent on how many lines are used in the "Company" box.

 

All the copy and paste are done and the macro returns to the first IE window http://www.mbaonline.org/MBAGeoLocator/Lots.aspx.

 

It is here that we have to select the next "More Details" button. This is our major problem. We are not trying to go thru all the pages listed as 1.2.3.4.5.6.7.8.9.10 etc. We are only trying to do the 10 "More Details" that are on the page. We would be happy to get this far.

 

You would think we would start the macro with the http://www.mbaonline.org/MBAGeoLocator/Lots.aspx but due to the "Company" box having different amount of lines we have to take a look first and than select the macro that works. They are all the same macro except for the copy we do in the "Company" box.

 

The following is the macro we use if there are 10 lines in the box.

 

// Resize 2nd Monitor to 1920 X 1200 before running macro

Window Reposition: Lot Report - Windows Internet Explorer Position: 1922, 2

Window Resize: Lot Report - Windows Internet Explorer Size: 860, 1162

Window Reposition: Metropolitan Builders Association : Members - Windows Internet Explorer Position: 2892, 2

Window Resize: Metropolitan Builders Association : Members - Windows Internet Explorer Size: 933, 1162

Window Activate: Lot Report - Windows Internet Explorer

Wait for Window Title: Lot Report - Windows Internet Explorer

Delay: 250 milliseconds

Mouse Move: 637, 283 Relative to Current Window

Delay: 250 milliseconds

Mouse Left Click

Mouse Left Button Down

Delay: 250 milliseconds

Mouse Move: 784, 490 Relative to Current Window

Delay: 250 milliseconds

Mouse Left Button Up

Clipboard Empty

Clipboard Copy

Delay: 250 milliseconds

Window Activate: UltraEdit -

Wait for Window Title: UltraEdit -

Clipboard Paste

Delay: 250 milliseconds

Text Type (Simulate Keystrokes): <ENTER>

Delay: 250 milliseconds

Text Type (Simulate Keystrokes): <CTRLD>f<CTRLU>

Delay: 250 milliseconds

Text Type (Simulate Keystrokes): <CTRLD>f<CTRLU>

Wait for Window Title: Find

Text Type (Simulate Keystrokes): Company:

Text Type (Simulate Keystrokes): <ENTER>

Text Type (Simulate Keystrokes): <DELETE>

Text Type (Simulate Keystrokes): <DELETE>

Delay: 250 milliseconds

Text Type (Simulate Keystrokes): <CTRLD>f<CTRLU>

Delay: 250 milliseconds

Text Type (Simulate Keystrokes): <CTRLD>f<CTRLU>

Wait for Window Title: Find

Text Type (Simulate Keystrokes): Contact Name:

Text Type (Simulate Keystrokes): <ENTER>

Text Type (Simulate Keystrokes): <DELETE>

Text Type (Simulate Keystrokes): <DELETE>

Delay: 250 milliseconds

Text Type (Simulate Keystrokes): <CTRLD>f<CTRLU>

Delay: 250 milliseconds

Text Type (Simulate Keystrokes): <CTRLD>f<CTRLU>

Wait for Window Title: Find

Text Type (Simulate Keystrokes): Contact Phone Number:

Text Type (Simulate Keystrokes): <ENTER>

Text Type (Simulate Keystrokes): <DELETE>

Text Type (Simulate Keystrokes): <DELETE>

Delay: 250 milliseconds

Text Type (Simulate Keystrokes): <CTRLD>f<CTRLU>

Delay: 250 milliseconds

Text Type (Simulate Keystrokes): <CTRLD>f<CTRLU>

Wait for Window Title: Find

Text Type (Simulate Keystrokes): Website:

Text Type (Simulate Keystrokes): <ENTER>

Text Type (Simulate Keystrokes): <DELETE>

Text Type (Simulate Keystrokes): <DELETE>

Delay: 250 milliseconds

// Company to T50

Text Type (Simulate Keystrokes): <CONTROL><HOME>

Text Type (Simulate Keystrokes): <SHIFT><END>

Clipboard Empty

Clipboard Copy

Delay: 150 milliseconds

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

// Contact Name to T51

Text Type (Simulate Keystrokes): <ARROW DOWN>

Text Type (Simulate Keystrokes): <HOME>

Text Type (Simulate Keystrokes): <SHIFT><END>

Clipboard Empty

Clipboard Copy

Delay: 150 milliseconds

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

// Phone Number to T52

Text Type (Simulate Keystrokes): <ARROW DOWN>

Text Type (Simulate Keystrokes): <HOME>

Text Type (Simulate Keystrokes): <SHIFT><END>

Clipboard Empty

Clipboard Copy

Delay: 150 milliseconds

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

// Website to T53

Text Type (Simulate Keystrokes): <ARROW DOWN>

Text Type (Simulate Keystrokes): <HOME>

Text Type (Simulate Keystrokes): <SHIFT><END>

Clipboard Empty

Clipboard Copy

Delay: 150 milliseconds

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

Text Type (Simulate Keystrokes): <CONTROL>a

Text Type (Simulate Keystrokes): <DELETE>

// Paste into Excel

Window Activate: MBA-Find a Lot.xlsx

Wait for Window Title: MBA-Find a Lot.xlsx

Wait for Left Mouse Click

Delay: 250 milliseconds

If Variable %T[50]% Exists ""

Text Type (Simulate Keystrokes): %T[50]%

Delay: 150 milliseconds

Text Type (Simulate Keystrokes): <ENTER>

Else

Text Type (Simulate Keystrokes): <ENTER>

End If

If Variable %T[51]% Exists ""

Text Type (Simulate Keystrokes): %T[51]%

Delay: 150 milliseconds

Text Type (Simulate Keystrokes): <ENTER>

Else

Text Type (Simulate Keystrokes): <ENTER>

End If

If Variable %T[52]% Exists ""

Text Type (Simulate Keystrokes): %T[52]%

Delay: 150 milliseconds

Text Type (Simulate Keystrokes): <ENTER>

Else

Text Type (Simulate Keystrokes): <ENTER>

End If

If Variable %T[53]% Exists ""

Text Type (Simulate Keystrokes): %T[53]%

Delay: 150 milliseconds

Text Type (Simulate Keystrokes): <ENTER>

Else

Text Type (Simulate Keystrokes): <ENTER>

End If

Macro Run: MBA-FindaLot

 

PS Thanks Heather for getting us this far. And all that have helped her along the way.

 

Bob

Link to comment
Share on other sites

I read the required procedure in the first post, Erratic Copy Command, Dec 3rd. Ignoring the code, the description underneath seems to be perfectly clear what is required. I followed the steps at the web site and it seemed straightforward.

 

I outlined the steps (full details omitted) that would do what was required. I also outlined the steps to take it even further (not asked for), to put each piece of information in it's own variable, to be used for whatever purpose. Each variable was specific to name, street address, city etc, not a blanket copy command.

 

All you would have to do is run the macro, wait for a beep or ding, click the "More Details" button and it would copy each line of the company info to a specific variable.

 

THIS thread is very confusing and it does not seem to me that you fully examined what I and others had written. I can't speak for anyone else but I spent a great deal of time on your problem.

Link to comment
Share on other sites

Thanks for taking the time to respond to my message.

 

I am going to check over my message now for inappropiate english.

 

I am really sorry, I copied my original message out to a text editor to check the spelling, but when I copied the good text back in, I didn't delete the original "bad" message. I apologize for the confusion!

 

Bob

Link to comment
Share on other sites

Complex macros like the one you are developing take a long time to develop and perfect. There are no shortcuts. Only one approach that I know of is effective at figuring out where a subtle (or a not-so-subtle) problem lie. Work iteratively: create a few lines of code to perform part of the task, and test repeatedly. When you are sure that it works, add more code to perform the next task, and test again. Slowly and surely, this approach yields results. But there are not substitutes for trial and error, time, and creativity. Overall, I would describe the approach as an incremental exercise in problem-solving.

 

If at some point, you discover a more reliable (or faster, or more elegant) way to perform a sub-task, you may or may not be able to salvage what you have done before. That's part of the dubious pleasure of macro development!

 

I had a quick look at your code, and although I don't understand the particulars of what you're trying to do, I think you are off to a good start. Adding more comments would help, but overall, it looks promising. But I would make one suggestion: See where you can change statements like this:

 

Text Type (Simulate Keystrokes): <CTRLD>f<CTRLU>

 

To this:

 

Text Type (Simulate Keystrokes): <CTRL>f

 

Over the years, I have learned to avoid <CTRLD> and <CTRLU>, as well as the analogous commands for Alt and Shift, unless absolutely required. I find they lead to unanticipated and unpredictable problems further on. If you are using this code to initiate a search, <CTRL>f will work in most contexts. The main "side effect" of this approach is figuring out how much of a delay to add before (rarely) or after (usually) the command to ensure that it works reliably enough.

Link to comment
Share on other sites

After posting my last comments and reading Alan's, it struck me that the codes posted by the "amerifax consortium" almost look like they have been recorded, although that may not be the case. This task is impossible to execute reliably with basic key and mouse actions.

 

Logic and manipulating variables are needed to extract the 5 items of information from the web page in the most reliable fashion. It's not very difficult and I outlined how to do it. However I was assuming a basic understanding of both logic (IF etc) and variables (finding position of text in a string).

 

Additionally, Ultra Edit is mentioned and I don't see why that would come into the picture. There could be a good reason I'm unaware of (such as an existing Ultra Edit script). If the extracted company information is needed somewhere else (say Excel or a database) it should be possible to enter using the ME macro. Ultra Edit itself can do many things that ME can do (keystrokes and scripting). I would have thought one or the other could do the entire job.

 

Selecting information on a web page with a mouse is certain to give problems, particularly in a multi-user environment. When you position the mouse on a page (using Mouse Move) it goes to a specific location. That location may become completely incorrect if the user has:

 

A different aspect monitor

The screen resolution is different

The text size is varied (user with poor eyesight)

The toolbar setup is different

No tabs versus tabs

 

This is why you try to use keystrokes. In this case, also employ logic and variables with the page source.

 

I don't want to be the fly in the ointment but I would hate to see a lot of time wasted going down a dead end. Hopefully Bob's review will shed some light.

 

Edit: As a final thought Bob, if I can convince you that you are going in the wrong direction, how about starting a new thread entitled something like "Extracting data from a web page" in which you would describe what you are really trying to do. That seems to be to extract the 5 pieces of company information from the web page (not the involved procedure that arises from your method of programming). You could add what the true end use is so that Ultra Edit can be bypassed if possible. If you put each item of data into its own variable, the possibilities what you can do with the information are unlimited. A thread with that sort of title would be more useful to someone else browsing for ideas to do same.

 

Lemming posted a similar thread in the Third Party Tools forum (first article "Extract all links...). Coincidentally I have a similar macro for commercial web pages except there is so much garbage to be removed that I use a text file with the list!

Edited by JohnS
Link to comment
Share on other sites

There are ways to "landmark" on a webpage so that text can be selected more reliably than than by mouse emulation.

 

1. This approach works when you need to copy text in an edit field. Search for unique text that is either just before or just after the target field, and then either send "Tab" or "Shift Tab," one or more times, to give keyboard input focus to the field. Unless there is some weird JavaScript happening under the hood, the text will be selected when focus arrives in the field.

 

Sometimes, cutting the text works more reliably than copying it.

 

2. Use Firefox. By changing certain program settings and installing certain add-ons, it is a snap to find, find again, and select by keyboard. I have built many macros that take advantage of Firefox's ability to give focus to select text via the keyboard.

Link to comment
Share on other sites

Alan, I think if you go to the company info page that is being interrogated you will find that Tab only finds one of the 5 lines of text (the web address). It's one of "those" pages. The page doesn't even have a menu bar (assumed javascript invoked).

 

Having said this task could not be done with keystrokes and mouse I found that I could get reasonable results from copying the entire text on the browser page and processing. All the pages I viewed have the first 4 items of information. It is the website that is sometimes missing. That can be handled with some logic. Using this method works as long as there is no word wrap in the box due to long addresses and that all the first 4 items are present.

 

I have both methods working. The copy text method is simpler to code. The source code method is more reliable in all circumstances.

 

I also use Firefox but it was easy to make versions for IE too. The shortcuts are different.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...