Loop to do Whitepages API calls?

What columns define the found rows as duplicate, M:P or N:P? Which of the imported columns do you want saved? Input data A:E should be enough to build your link. Maybe you could post an example of what the output sheet should look like. Obviously, the easier method is to use a scratch sheet and do each run and then get the rows as needed for a summary sheet.

Thanks so much for helping! I've attached a spreadsheet with a second sheet that has the output from the static API call. In the standard format which Excel copies, Cell's A2 and C2 easily tell if the result was a success and what was found, so if not success I'm only interested in pasting that field and nothing else. For successes, I'm only interested in the lines that have names returned on them, and the rank collumn (M) seems to only have something in it on the lines I want. I really only want columns M through AD copied. I was thinking the macro could check for duplicates because we're only dealing with a few names per API hit usually, but that's not a huge requirement. Great idea about a scratch sheet, I was wondering if for each call it could copy into a scratch sheet and then copy into a final list.

Time for sleep so I will post what I have so far. It should be about 80% of what you need and should let us know if we are close.

I named the first sheet RawData, the 2nd GetDetails, and Scratch for the 3rd sheet. I commented out the full For loop and put in a loop for the first 5 rows. In this way, you can test without wasting too much time.

I added a 2nd routine that lets you keep the imported XML to a new sheet named A2Sheet for the first RawData row starting with cell A2, A3Sheet for cell A3 and so on. This is just for testing purposes. The first button does the copy and paste from the Scratch sheet where each XML is imported for the main routine. It then removes duplicates. It is a bit off on some rows so I will have to see why later.

Please Login or Register  to view this content.

My standard Speed routine is in another Module:

Please Login or Register  to view this content.

Thanks so much, I'm going to try this out today.

Kenneth - I'm getting a runtime error, '9' subscript out of range. The debugger points to this line:

Set rdSheet = ThisWorkbook.Worksheets("RawData")

I'm sure it's just something I should have put in there in addition to your code, thanks so much for your help.

You can rename your sheet with the raw data to RawData or change that code to contain the raw data's sheet name. You will need the GetDetails sheet name and the Scratch sheet names as well or change the sheet names in the code.

Kenneth - whenever you get a chance, would you be able to attach the spreadsheet as you're using it? I have the sheet open and renamed the first sheet "RawData", the second sheet "GetDetails", and the third sheet "Scratch" and it's still doing the same thing. Could it be that I'm missing some module of VB that's important for this? I just copied your code directly into a module in excel's VB editor.

I zipped the file and attached it earlier in the code snippet post. Are zip files a problem for you?

No, I just missed seeing the attachment. Got it, and yours does work. I'll take a look and let you know if I have any other questions. Thanks!

Kenneth - This is great, really neat! I'm trying to figure out how to make it go all the way down the list but I'm sure I can figure it out. I showed to my boss and it may be enough of a proof of concept to get some programming resources to help out with this. Thanks very much!

Good deal.

I said earlier that you can process all of the entries. All you have to do is to comment out or delete the For line of code and uncomment the For below it.

If I get time, I will look into using WinHTTP or this method which is similar:

Please Login or Register  to view this content.

Sorry Kenneth, I missed that. It's awsome! I took programming in college and used to do Basic but I'm afraid my skills are sorely lacking since I've been doing sales forever. Mea culpa, and thanks again.

How do I set this thread as Solved? Thanks Kenneth!

From the FAQ:

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

Here is one final post for those that might want to parse the xml data. This is just a primer to get you started. If you open the xml file and review the Immediate Window's results after you run the two subs, you can start to see the structure and how to get specific parts. Run Test_WhitePagesInfo, open the created WhitePages.xml, and then run Test_ParseXMLWhitePageData. Compare the xml file's data to the Immediate Window.

You will need get your own api_key as stated earlier. Be sure to set the Microsoft XML, v6.0 reference.

Please Login or Register  to view this content.