Multiple Google Searches to Excel (or Similar)

[john.grisum]

I have a little side project that I need to complete and would like some input. I have a list of about 3,000 excel entries that i need to find their matching sibling (I currenlty have over 11,000). I have included examples below where the MEP has a sibling PRD. MEP-04103-001 PRD-49028-005MEP-04103-001 PRD-49028-006MEP-04103-001 PRD-49028-007MEP-04103-001 PRD-49028-010MEP-04103-001 PRD-49028-011 I have several thousand PRD numbers that do not have their sibling MEP numbers and would need to search the web for such a match. I have been doing this manually but I will have several hundred to do each month and it will become quite tedious. I have been doing manual web searches by using this format: "PRD-09854-019" NEAR "MEP-*****-***". What I need is to be able to create an excel or similar document to include web searches for the PRD and include perhaps the top 5 results' description section and web link. Any ideas would be greatly appreciated.

Edited October 12, 2012 by john.grisum

[birbal]

You will need to basicaly crawl web pages. i dont know google will permit you to do that or not. http:///php.net/curl...e_get_contents() can be used to get web pages remotely. you can also load remote pages using DOM and parse the page to get first 5 results from it. you have to analyze how the page structure is of googe to parse it correctly. once yu parse and got the data you can either use CSV file to store using file fpucsv() for using http://php.net/com to directly create excel file from ms excel or possiblay there is php 3rd party library for creating excel pages (you can google it)

Edited October 12, 2012 by birbal

[justsomeguy]

If you want to write a macro in Excel to send web requests this should get you started: http://stackoverflow.com/questions/158633/how-can-i-send-an-http-post-request-to-a-server-from-excel-using-vba

[john.grisum]

Ive slimmed down to the PRD & # of hits columns. This makes it much easier for me to focus on which PRDs are good and which are not.

[john.grisum]

Just an update. I have used a VB script via excel to undergo a search for the content in A# and display the number of hits in B#. It actually works quite well. The code is below if anyone wants to use it. Example output: [A1] "PRD-12345-123" [B1] 708 Hits

Public Sub ExcelGoogleSearch()Dim searchWords As StringWith Sheets("Sheet1")RowCount = 1Do While .Range("A" & RowCount) <> ""searchWords = .Range("A" & RowCount).Value' Get keywords and validate by adding + for spaces betweensearchWords = Replace$(searchWords, " ", "+")' Obtain the source code for the Google-searchterm webpagesearch_url = "http://www.google.com/search?hl=en&q=" & searchWords & "&meta="""Set search_http = CreateObject("MSXML2.XMLHTTP")search_http.Open "GET", search_url, Falsesearch_http.sendresults_var = search_http.responsetextSet search_http = Nothing' Find the number of results and post to sheetpos_1 = InStr(1, results_var, "resultStats>", vbTextCompare)pos_2 = InStr(3 + pos_1, results_var, ">", vbTextCompare)pos_3 = InStr(pos_2, results_var, "<nobr>", vbTextCompare)NumberofResults = Mid(results_var, 1 + pos_2, (-1 + pos_3 - pos_2))Range("B" & RowCount) = NumberofResultsRowCount = RowCount + 1LoopEnd WithEnd Sub

Edited October 16, 2012 by john.grisum