Jump to content
Macro Express Forums

Need help creating macro for google search funtion


gotteeth

Recommended Posts

Hello,

 

Thanks in advance for any help offered.

 

I have Macro Express and I need to create a macro but what I need is out of my experince level and I don't have the time to figure it out.

 

I need advice on how to get this done and what a reasonable price for payment would be.

 

The macro would need to start from an open excel file. There will be a random number of keywords in the file, one keyword phrase on each line...

 

bannanas

green bannanas

blue bannanas

 

I need the macro to highlight each of those keyword phrases individually, go to google and type it in the search box. I will need four different searches for each keyword phrase, with the values from each search copied from google and pasted to the excel file.

 

Here is what the searches will be:

 

"bannanas"

intitle:”bannanas” inanchor:”bannanas”

site:squidoo.com intitle:"bannanas" inanchor:"bannanas"

site:hubpages.com intitle:"bannanas" inanchor:"bannanas"

 

There needs to be a random time delay between each search, or at least 5 seconds.

 

The above seems possible with MacroExpress...I just don't know how to set it up.

 

Thanks again for any help.

Link to comment
Share on other sites

The macro would need to start from an open excel file. There will be a random number of keywords in the file, one keyword phrase on each line...

 

bannanas

green bannanas

blue bannanas

 

I need the macro to highlight each of those keyword phrases individually, go to google and type it in the search box. I will need four different searches for each keyword phrase, with the values from each search copied from google and pasted to the excel file.

 

Here is what the searches will be:

 

"bannanas"

intitle:”bannanas” inanchor:”bannanas”

site:squidoo.com intitle:"bannanas" inanchor:"bannanas"

site:hubpages.com intitle:"bannanas" inanchor:"bannanas"

 

There needs to be a random time delay between each search, or at least 5 seconds.

 

The above seems possible with MacroExpress...I just don't know how to set it up.

 

This shouldn't be too difficult (if you've played with ME for a while), and I'd be happy to help you build the macro you need. But I need just a little more informaiton. Specifically, when you say: "...with the values from each search copied from google and pasted to the excel file." To what values are you referring?

 

Also you say there needs to be a random time delay or at least 5 seconds between each search. Is that in addition to the time required for the macro to perform the Google search, collect the information, and save it to the Excel file? If so, why does there need to be an additional 5 seconds?

Link to comment
Share on other sites

This shouldn't be too difficult (if you've played with ME for a while), and I'd be happy to help you build the macro you need. But I need just a little more informaiton. Specifically, when you say: "...with the values from each search copied from google and pasted to the excel file." To what values are you referring?

 

Also you say there needs to be a random time delay or at least 5 seconds between each search. Is that in addition to the time required for the macro to perform the Google search, collect the information, and save it to the Excel file? If so, why does there need to be an additional 5 seconds?

 

 

Thanks for your help on this.

 

1. The "values" I'm referring to are the search results that show in google after the search. Example: go to google and do a search for the word banana, look in the upper right side of the results in google, you'll see "Results 1 - 10 of about 58,200,000 for banana." I'm after the value, that in this example, is 58,200,000.

 

2.The delay I spoke about is needed only because google sees fast, repetitive queries as automated and will respond by requiring a captcha be filled in before it responds. It's a pain in the rear and can be avoided by going slow with the query speed. I'm going to be searching for small amounts of data so this shouldn't be too much of an issue.

 

Does that better explain it?

 

Thank you so much for your input/help.

 

Cheers!

 

Mike

Link to comment
Share on other sites

Thanks for your help on this.

 

1. The "values" I'm referring to are the search results that show in google after the search. Example: go to google and do a search for the word banana, look in the upper right side of the results in google, you'll see "Results 1 - 10 of about 58,200,000 for banana." I'm after the value, that in this example, is 58,200,000.

 

2.The delay I spoke about is needed only because google sees fast, repetitive queries as automated and will respond by requiring a captcha be filled in before it responds. It's a pain in the rear and can be avoided by going slow with the query speed. I'm going to be searching for small amounts of data so this shouldn't be too much of an issue.

 

Does that better explain it?

 

Thank you so much for your input/help.

 

Cheers!

 

Mike

Ok Mike, I think I've got a solution for you. It's not as perfect as I wanted, but it works pretty darn well. I have a short list of caveats, however.

 

  1. This won't pull from or write directly to an open .xls document. You'll need to save the .xls document that your list appears in as a .csv document. I am (or rather was)
    sure that Macro Express could do this for you, creating a .csv that it would work from and then getting rid of it automatically (behind the scenes), but I can't for the life
    of me get it to work.
    • So, first from your Excel program (with the list open - we'll call it Bananas.xls): File > Save As > Bananas.csv (be certain to select CSV from the drop-down list,
      otherwise Excel may save it as Bananas.csv.xls - which won't work)
    • Second, open the macro in the Macro Expess Editor and make changes to the first 2 commands: Change the location of the first Variable Set String commands to
      the correct locations on your machine (and change the names to match your files) - Please make careful note: the first one (%T20%) is the name of the .csv you just
      created (Bananas.csv), the second one (%T21%) needs to be different - I just added a '1' to the end of the name (Bananas1.csv). This is because the macro cannot
      write to the same file that it is reading from. So it will read from %T20% and write to %T21%. When it's all finished, the information will be moved to %T20% and
      %T21% will be deleted.
    • Third, close the new .csv file (if it is open, the macro won't do anything). You may have to click a couple of Yes buttons when closing the .csv.

[*]This works on my XP Pro machine set to a resolution of 1040 x 1280. This is important because in order to find the "Value" information, the macro has to look for something

consistent. In this case, it's the light-blue highlight. I've got a slight fool-proof built in so that the macro starts looking for it too high, and then proceeds to search down the

screen until it finds it, but if the macro doesn't work for you, you may need to make adjustments to the %N2% variable (which is where the macro starts it's vertical search

down the page - currently set to 200)

[*]My Google is set with default colors (I'm not sure if there are other colors available, but this macro relies on detecting the light-blue bar highlighting the line where the values

appear). If you are using something other than the default colors, you'll need to figure out the color and reassign it to the %N3% variable in the Repeat Until commands

throughout the macro.

[*]One last caveat. This macro relies on Internet Explorer. Any other browser will probably not work. Macro Express is very limited as to which browsers are supported (IE

being the only one).

 

Finally, I tested this using only the three sample items you gave in your list (bananas, green bananas, and blue bananas). I never had Google get mad at me for too many

searches. Maybe a longer list will cause problems. If so, you should only need to enable the 5 second delay close to the end of the script (I built it in there, but left it disabled

because it wasn't necessary). The macro will perform the 4 searches per line, then delay before moving on to the next line.

 

Once the macro has finished running, you can open the .csv file and File > Save as > Bananas.xls

 

You'll be prompted to replace the existing Bananas.xls > Choose Yes and you're done (except deleting the Bananas.csv file, of course)!

 

Here is the direct editor script (just copy this and paste it into a blank macro):

 

<TVAR2:20:01:C:\Documents and Settings\z051940\Desktop\Bananas for GotTeeth.csv><TVAR2:21:01:C:\Documents and Settings\z051940\Desktop\Bananas for GotTeeth1.csv><CLIPE><IVAR2:10:01:1><ADFBEG:F10:001:000001:000000:%T20%><HTTP2:1:T:http://www.google.com/search?hl=en&q=%T1%><IVAR2:01:09:><NMVAR:04:01:1:0000001:2:0000002><IVAR2:02:01:200><REP3:08:000001:000002:0003:0:01:16381936><NMVAR:01:02:1:0000002:2:0000005><GETPX:3:W:%N1%:%N2%><ENDREP><MMW2:1N,2N><LDCLK><LCLK><CLIPC><REP3:01:000001:000001:00010:1:04:><IFOTH2:07:1:><DELAY:.05><ELSE><BREAK><ENDIF><IFVAR2:2:04:1:10><WCLS:%T1%><MACRUN2:GotTeeth - Bananas><ENDIF><ENDREP><TVAR2:02:03:><TMVAR2:01:02:00:000:000:><IVAR2:04:13:2: of about ><TMVAR2:11:02:00:001:N04:><TMVAR2:21:02:00:000:000:of about><IVAR2:05:13:2: for %T1%><TMVAR2:10:02:02:001:N05:><IFVAR2:1:02:7: of ><IVAR2:06:13:2: of ><TMVAR2:11:02:00:001:N06:><TMVAR2:21:02:01:000:000:of><ENDIF><TMVAR2:01:02:00:000:000:><TMVAR2:21:02:01:000:000:,><TVAR2:10:01:,%T2%><WCLS:%T1%><HTTP2:1:T:http://www.google.com/search?hl=en&q=intitle:%T1%+inanchor:%T1%><IVAR2:01:09:><NMVAR:04:01:1:0000001:2:0000002><IVAR2:02:01:200><REP3:08:000001:000002:0003:0:01:16381936><NMVAR:01:02:1:0000002:2:0000005><GETPX:3:W:%N1%:%N2%><ENDREP><MMW2:1N,2N><LDCLK><LCLK><CLIPC><REP3:01:000001:000001:00010:1:04:><IFOTH2:07:1:><DELAY:.05><ELSE><BREAK><ENDIF><ENDREP><IFVAR2:2:04:1:10><WCLS:%T1%><MACRUN2:GotTeeth - Bananas><ENDIF><TVAR2:02:03:><TMVAR2:01:02:00:000:000:><IVAR2:04:13:2: of about ><TMVAR2:11:02:00:001:N04:><TMVAR2:21:02:00:000:000:of about><IVAR2:05:13:2: for ><TMVAR2:10:02:02:001:N05:><IFVAR2:1:02:7: of ><IVAR2:06:13:2: of ><TMVAR2:11:02:00:001:N06:><TMVAR2:21:02:01:000:000:of><ENDIF><TMVAR2:01:02:00:000:000:><TMVAR2:21:02:01:000:000:,><TMVAR2:07:10:00:000:000:, %T2%><WCLS:%T1%><HTTP2:1:T:http://www.google.com/search?hl=en&q=site:squidoo.com+intitle:%T1%+inanchor:%T1%><IVAR2:01:09:><NMVAR:04:01:1:0000001:2:0000002><IVAR2:02:01:200><REP3:08:000001:000002:0003:0:01:16381936><NMVAR:01:02:1:0000002:2:0000005><GETPX:3:W:%N1%:%N2%><ENDREP><MMW2:1N,2N><LDCLK><LCLK><CLIPC><REP3:01:000001:000001:00010:1:04:><IFOTH2:07:1:><DELAY:.05><ELSE><BREAK><ENDIF><IFVAR2:2:04:1:10><WCLS:%T1%><MACRUN2:GotTeeth - Bananas><ENDIF><ENDREP><TVAR2:02:03:><TMVAR2:01:02:00:000:000:><IVAR2:04:13:2: of about ><TMVAR2:11:02:00:001:N04:><TMVAR2:21:02:00:000:000:of about><IVAR2:05:13:2: for ><TMVAR2:10:02:02:001:N05:><IFVAR2:1:02:7: of ><IVAR2:06:13:2: of ><TMVAR2:11:02:00:001:N06:><TMVAR2:21:02:01:000:000:of><ENDIF><TMVAR2:01:02:00:000:000:><TMVAR2:21:02:01:000:000:,><TMVAR2:21:02:01:000:000:from squidoo.com><TMVAR2:07:10:00:000:000:, %T2%><WCLS:%T1%><HTTP2:1:T:http://www.google.com/search?hl=en&q=site:hubpages.com+intitle:%T1%+inanchor:%T1%><IVAR2:01:09:><NMVAR:04:01:1:0000001:2:0000002><IVAR2:02:01:200><REP3:08:000001:000002:0003:0:01:16381936><NMVAR:01:02:1:0000002:2:0000005><GETPX:3:W:%N1%:%N2%><ENDREP><MMW2:1N,2N><LDCLK><LCLK><CLIPC><REP3:01:000001:000001:00010:1:04:><IFOTH2:07:1:><DELAY:.05><ELSE><BREAK><ENDIF><IFVAR2:2:04:1:10><WCLS:%T1%><MACRUN2:GotTeeth - Bananas><ENDIF><ENDREP><TVAR2:02:03:><TMVAR2:01:02:00:000:000:><IVAR2:04:13:2: of about ><TMVAR2:11:02:00:001:N04:><TMVAR2:21:02:00:000:000:of about><IVAR2:05:13:2: for ><TMVAR2:10:02:02:001:N05:><IFVAR2:1:02:7: of ><IVAR2:06:13:2: of ><TMVAR2:11:02:00:001:N06:><TMVAR2:21:02:01:000:000:of><ENDIF><TMVAR2:01:02:00:000:000:><TMVAR2:21:02:01:000:000:,><TMVAR2:21:02:01:000:000:from hubpages.com><TMVAR2:07:10:00:000:000:, %T2%><WCLS:%T1%><TMVAR2:07:01:00:000:000:%T10%><IFVAR2:2:10:1:1><TMVAR2:17:01:00:000:000:%T21%T><ELSE><TMVAR2:20:01:00:000:000:%T21%T><ENDIF><NMVAR:08:10:0:0000001:0:0000000><DIS:<DELAY:5><ADFEND><DOFILE:05:NN:%T21%>%T20%><DOFILE:08:NN:%T21%>>

 

In the Script Editor it will look something like this:

Variable Set String %T20% "C:\Documents and Settings\z051940\Desktop\Bananas for GotTeeth.csv"
Variable Set String %T21% "C:\Documents and Settings\z051940\Desktop\Bananas for GotTeeth1.csv"
Clipboard Empty
Variable Set Integer %N10% to 1
ASCII File Begin Process: "%T20%" (Comma Delimited Text )
 Web Site: http://www.google.com/search?hl=en&q=%T1% [Default Browser - Wait to load]
 Variable Set Integer %N1% from Width of Window
 Variable Modify Integer: %N1% = %N1% / 2
 Variable Set Integer %N2% to 200
 Repeat Until %N3% = 16381936
Variable Modify Integer: %N2% = %N2% + 5
Get Pixel: Window Coords: %N1%,%N2% into %N3%
 Repeat End
 Mouse Move Window %N1%, %N2%
 Mouse Left Button Double Click
 Mouse Left Button Click
 Clipboard Copy
 Repeat Start (Repeat 10 times)
If Clipboard Text Equals ""
  Delay 0.05 Seconds
Else
  Break
End If
If Variable %N4% = 10
  Window Close: "%T1%"
  Macro Run: GotTeeth - Bananas
End If
 Repeat End
 Variable Set String %T2% from Clipboard
 Variable Modify String: Trim %T2%
 Variable Set Integer %N4% from Position of Text in Variable %T2%
 Variable Modify String: Delete Part of %T2%
 Replace "of about" with "" in %T2%
 Variable Set Integer %N5% from Position of Text in Variable %T2%
 Variable Modify String: Copy Part of %T2% to %T2%
 If Variable %T2% contains " of "
Variable Set Integer %N6% from Position of Text in Variable %T2%
Variable Modify String: Delete Part of %T2%
Replace "of" with "" in %T2%
 End If
 Variable Modify String: Trim %T2%
 Replace "," with "" in %T2%
 Variable Set String %T10% ",%T2%"
 Window Close: "%T1%"
 Web Site: http://www.google.com/search?hl=en&q=intitle:%T1%+inanchor:%T1% [Default Browser - Wait to load]
 Variable Set Integer %N1% from Width of Window
 Variable Modify Integer: %N1% = %N1% / 2
 Variable Set Integer %N2% to 200
 Repeat Until %N3% = 16381936
Variable Modify Integer: %N2% = %N2% + 5
Get Pixel: Window Coords: %N1%,%N2% into %N3%
 Repeat End
 Mouse Move Window %N1%, %N2%
 Mouse Left Button Double Click
 Mouse Left Button Click
 Clipboard Copy
 Repeat Start (Repeat 10 times)
If Clipboard Text Equals ""
  Delay 0.05 Seconds
Else
  Break
End If
 Repeat End
 If Variable %N4% = 10
Window Close: "%T1%"
Macro Run: GotTeeth - Bananas
 End If
 Variable Set String %T2% from Clipboard
 Variable Modify String: Trim %T2%
 Variable Set Integer %N4% from Position of Text in Variable %T2%
 Variable Modify String: Delete Part of %T2%
 Replace "of about" with "" in %T2%
 Variable Set Integer %N5% from Position of Text in Variable %T2%
 Variable Modify String: Copy Part of %T2% to %T2%
 If Variable %T2% contains " of "
Variable Set Integer %N6% from Position of Text in Variable %T2%
Variable Modify String: Delete Part of %T2%
Replace "of" with "" in %T2%
 End If
 Variable Modify String: Trim %T2%
 Replace "," with "" in %T2%
 Variable Modify String: Append ", %T2%" to %T10%
 Window Close: "%T1%"
 Web Site: http://www.google.com/search?hl=en&q=site:squidoo.com+intitle:%T1%+inanchor:%T1% [Default Browser - Wait to load]
 Variable Set Integer %N1% from Width of Window
 Variable Modify Integer: %N1% = %N1% / 2
 Variable Set Integer %N2% to 200
 Repeat Until %N3% = 16381936
Variable Modify Integer: %N2% = %N2% + 5
Get Pixel: Window Coords: %N1%,%N2% into %N3%
 Repeat End
 Mouse Move Window %N1%, %N2%
 Mouse Left Button Double Click
 Mouse Left Button Click
 Clipboard Copy
 Repeat Start (Repeat 10 times)
If Clipboard Text Equals ""
  Delay 0.05 Seconds
Else
  Break
End If
If Variable %N4% = 10
  Window Close: "%T1%"
  Macro Run: GotTeeth - Bananas
End If
 Repeat End
 Variable Set String %T2% from Clipboard
 Variable Modify String: Trim %T2%
 Variable Set Integer %N4% from Position of Text in Variable %T2%
 Variable Modify String: Delete Part of %T2%
 Replace "of about" with "" in %T2%
 Variable Set Integer %N5% from Position of Text in Variable %T2%
 Variable Modify String: Copy Part of %T2% to %T2%
 If Variable %T2% contains " of "
Variable Set Integer %N6% from Position of Text in Variable %T2%
Variable Modify String: Delete Part of %T2%
Replace "of" with "" in %T2%
 End If
 Variable Modify String: Trim %T2%
 Replace "," with "" in %T2%
 Replace "from squidoo.com" with "" in %T2%
 Variable Modify String: Append ", %T2%" to %T10%
 Window Close: "%T1%"
 Web Site: http://www.google.com/search?hl=en&q=site:hubpages.com+intitle:%T1%+inanchor:%T1% [Default Browser - Wait to load]
 Variable Set Integer %N1% from Width of Window
 Variable Modify Integer: %N1% = %N1% / 2
 Variable Set Integer %N2% to 200
 Repeat Until %N3% = 16381936
Variable Modify Integer: %N2% = %N2% + 5
Get Pixel: Window Coords: %N1%,%N2% into %N3%
 Repeat End
 Mouse Move Window %N1%, %N2%
 Mouse Left Button Double Click
 Mouse Left Button Click
 Clipboard Copy
 Repeat Start (Repeat 10 times)
If Clipboard Text Equals ""
  Delay 0.05 Seconds
Else
  Break
End If
If Variable %N4% = 10
  Window Close: "%T1%"
  Macro Run: GotTeeth - Bananas
End If
 Repeat End
 Variable Set String %T2% from Clipboard
 Variable Modify String: Trim %T2%
 Variable Set Integer %N4% from Position of Text in Variable %T2%
 Variable Modify String: Delete Part of %T2%
 Replace "of about" with "" in %T2%
 Variable Set Integer %N5% from Position of Text in Variable %T2%
 Variable Modify String: Copy Part of %T2% to %T2%
 If Variable %T2% contains " of "
Variable Set Integer %N6% from Position of Text in Variable %T2%
Variable Modify String: Delete Part of %T2%
Replace "of" with "" in %T2%
 End If
 Variable Modify String: Trim %T2%
 Replace "," with "" in %T2%
 Replace "from hubpages.com" with "" in %T2%
 Variable Modify String: Append ", %T2%" to %T10%
 Window Close: "%T1%"
 Variable Modify String: Append "%T10%" to %T1%
 If Variable %N10% = 1
Variable Modify String: Save %T1% to Text File
 Else
Variable Modify String: Append %T1% to Text File
 End If
 Variable Modify Integer: Inc (%N10%)
 Delay 5 Seconds
ASCII File End Process
Copy File or Files: "%T21%"
Delete File or Files: "%T21%"

Link to comment
Share on other sites

Steve,

 

I have not had time to play with this. I just quickly read the post.

 

Please PM me so I can send you some beer money.

 

I really appreciate your time and input on this. This is something that's been on my "to-do" list for a long time, and not having a way to retrieve the data I need has really slowed me down.

 

I'll go over this this weekend and see if I can make it work without screwing it up :)

 

Cheers!

 

Mike

Portland, Oregon

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...
×
×
  • Create New...