Welcome to JustAnswer. My name is XXXXX XXXXX I will be assisting you today. Please do not rate this session until it has been completed.
Do you want this to be done for you, or do you want some tutorials that will show you how to do it?
Hello Bryan - I just want this done for me - as in if you can create a function that I can copy and start using that would be ok.
I anticipate using it several times in an excel sheet and go through maybe a hundred websites and 30 keywords - so a function that can take a webseite name from an excel cell and a keyword from another cell and do a google search and return the number of times the keyword was found in that particular website. I can do this manually - just do not know how to put all this into excel. In google search I use the site:sitenamehere.com keyword option and it works.
Ok, well this isn't a quick function to write, that's the only problem. But if you want to send me the spreadsheet (or even just a sample spreadsheet with like 5 rows or something) then I can get going on it a little later tonight. Although the price is a bit low for this. I would have to submit a request for price adjustment. However, if you would like tutorials, I can get you those to see if you can walk through it yourself and then you can let me know. Does that sound okay?
We can talk price adjustment if needed - I am ok with that. In either case what you create for me I may decide to fine tune
so let me send you an sample spreadsheet ..
Ok. You can either attach it here using the paperclip, or through wikisend.com.
I am open to tutorials but do not want to spend too much time reading and then finding out I cannot do it myself .. ha..ha..
No problem at all. Just wanted to present the option. :)
I'm going to have to run in a couple minutes. But if you haven't sent the spreadsheet before I go, I'll grab it as soon as I return in about an hour.
I better attach the spreadsheet somehow .. how can I do that ?
It would be best if you could zip and attach the actual spreadsheet.
I haven't zipped files on this computer ever .. how do I do that ?
Are you on Windows?
WindowsKey+e, find the spreadsheet file, right-click and select "Send To - Compressed (zipped folder)"
ok zipped it .. now do i attach or email ?
Attach it using the paperclip icon
did it make it ?
Yep, got it.
I'll get started on this in about an hour or so and write back once I've got something for you, okay?
I've run into a little problem. It's against the JustAnswer rules for me to violate another companies terms of service. And this was just pointed out to me:5.3 You agree not to access (or attempt to access) any of the Services by any means other than through the interface that is provided by Google, unless you have been specifically allowed to do so in a separate agreement with Google. You specifically agree not to access (or attempt to access) any of the Services through any automated means (including use of scripts or web crawlers) and shall ensure that you comply with the instructions set out in any robots.txt file present on the Services.So, while I'm not allowed to do this myself, I am allowed to offer limited guidance. The normal web search query in Excel will not do what you want. It will only return the actual page. But, there is a Google AdWords API that is easily integrated into Excel. Please take a look at this page, download and install the API, go as far as you can and let me know if you have questions. I'll do my very best to help, so long as I don't violate the Google TOS.
Let me know if you get stuck.
ok - thanks I will give it a try.
But will I have to write some VB code to make this work in excel ?
There are examples in the API that you download. And examples on the page I linked so it should be easyy to follow and change for your own needs. But, please feel free to ask me about some specifics if you get stuck.
The examples of the API usage that I saw on the video are looking at the whole web or mobile devices within a country.
I am interested in specific websites..do the APIs support that ?
Also I am getting stuck trying to get past the Adwords API Center registration - even though I have two existing
Yeah, the intro page explains that it's easier to just create another account for that. And I believe that as long as you leave out the country parameter, it will search the default site.
And what would the default site be .. do we specifiy it ?
Yes, as a parameter.
ok - I will keep trying to register.
Got past registration for Adwords API access .. now which if the 5 or 6 APIs am I to download ? New Java ?
which of the
Downloaded everything I need .. I think .. but get error messages when I try some things.
Chat with you when you get back online ..late night here.
So in my excel spreadsheet, I try to make the getAdWords function work like it is supposed to with regular parameters and I get error message right in excel that says "Error:System.Reflection.TargetInvocationException - Could not find file 'C:\Users\HOMEPC-9\Local\Temp\ei4ykvdw.dll'.
I was trying =getAdWords("catfood","STATS","EXACT","US","MOBILE").
This is the error I am getting this morning - but last night I was getting an authentication error on the same spot before I shut things down for the night.
Under HOMEPC-9 ther rest of the folders cannot be found ..there are lots of other folders but not the one mentioned in the error message.
But now this morning I am gettting the same erroe message I was getting last night .. "TargetingIdeaService.get Error: System.Net.WebException - Authentication failed because the remote party has closed the transport stream.
Another funny thing - I cannot get to google.com !!
I must have changed some settings ..when I try to go to google.com I get a SSL connection error ..
Can make it to google.com now.
I did uninstall the APIs.
Still getting the error :"TargetingIdeaService.get Error: System.Net.WebException - Authentication failed because the remote party has closed the transport stream.
Looks like I am getting past those earlier erros - now it is complaining about an invalid_token_trigger - I could not find anything called "developer token" so I used what appeared to be the adWords account number or Customer ID ...where would I find the exact "developer token" ?
I am back at this problem again - have not been able to resolve it yet.
The google adwords approach is probably more comprehensive but I have not been able to setup the account and all that. It seems to be a longer route to solve my problem. I put in all the requests for the accounts etc. but have not got any response. I may have to walk through the instructions all over again and still wait another week to get a response from google. If ultimately that is what I need to do I will do that.
However, I have found something close enough on stackoverflow.com that just needs a small modification. So here is your task :
1) Look at this code and walk me through steps to get this working as is. I am not an expert in Excel or VB but given some instructions can cut and paste some code and make it run if someone can walk me through. Last time I did programming was about 20 years ago.
2) Help me modify the google search portion where instead of searching the whole internet, I am searching in a particular website, which is named in say Column D (D1, D2, D3 etc.)
Let me know what the fee is and lets move on this.
============= Excerpt from stackoverflow.com===============
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)If pos_1 = 0 Then NumberofResults = 0Else 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))End IfRange("B" & RowCount) = NumberofResultsRowCount = RowCount + 1LoopEnd WithEnd Sub
Some additional url's :
However, I would prefer to stay with google search if possible.
Ok thanks I understand. I will check once again why my developer account has not been approved by Google yet. Only after that approval can I try the Adwords approach. Until then if you can make that code work that will be excellent.
I can be flexible about it, but lets go with
URL is in first column (A1, A2, A3 etc.),
word to search is in second column (B1, B2, B3 etc.),
and result count is posted in third column (C1, C2, C3 etc.)
Great. I have downloaded the code on my machine.
I will need some further instructuctions on how to import it into my VB environment in Excel and start using it as a Macro etc.
I can toggle between excel and the VB environment but beyond that do not know how to import this file and make it work.
For now we can set it up so that I get the data in place (say 20 websites in first column and one keyword along each website) and then select to run the macro - that would work.
Once I see how exactly it is working we may refine / optimize how it needs to work.
Yes it is Office 2010.
I get a "permission to use object denied" error message from VB at the last step ("Run").
I get the error msg on Ln 20, Col1
OK thanks, I will pursue the google API key.