gotteeth Posted May 26, 2009 Report Share Posted May 26, 2009 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. Quote Link to comment Share on other sites More sharing options...
stevecasper Posted May 28, 2009 Report Share Posted May 28, 2009 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? Quote Link to comment Share on other sites More sharing options...
gotteeth Posted May 28, 2009 Author Report Share Posted May 28, 2009 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 Quote Link to comment Share on other sites More sharing options...
stevecasper Posted May 29, 2009 Report Share Posted May 29, 2009 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. 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%" Quote Link to comment Share on other sites More sharing options...
gotteeth Posted May 29, 2009 Author Report Share Posted May 29, 2009 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.