Web Scrap name from a webpage

I have a spreadsheet with various identifying numbers (called NPIs) in column A (see attached). I am using the following website to lookup the name for each NPI number and place in column B:

https://npiregistry.cms.hhs.gov/

I notice after I type in the NPI number and click search, the resulting URL is always the same: https://npiregistry.cms.hhs.gov/regi...iew/xxxxxxxxxx with the x value being the NPI number. Is there a way to extract the name automatically without having to lookup each provider?

Alternatively, I notice they have an API do accomplish this with JSON, but not sure how to integrate with Excel. The API webapge is at the following URL:

https://npiregistry.cms.hhs.gov/registry/help-api

Finally, I had solved this some time ago with another method as outlined in the link Excelforum.com link below, but they recently changed the website around which make the solution no longer work.

http://www.excelforum.com/showthread...80#post3539780

Any help is appreciated...

Hello,

1. Are you using Excel 64 bit (note this is not the same as windows 64-bit)
2. Do you need this to work on a mac?

I Created a Function that gets the Resultstring from the API Website and then searches for the Name in the JSON String.
Function Returns either
"NPI not found" if there are no Results
"Error in Request" if there is another problem
Or the Name if everything worked

As far as i know there are specific librarys that can handle Json but i went with a litttle mid and instr magic.
I guess Regex would also work but i am not good with that :D

Public Function getNpi(Number As String)
    On Error GoTo errorhandler:
    
    'Number = 1245319599
    Web_URL = "https://npiregistry.cms.hhs.gov/api/?number=" & Number
 
 
    With CreateObject("msxml2.xmlhttp")
        .Open "GET", Web_URL, False
        .send
        resultstring = .responseText
    End With
    
    emptyresult = "{" & """" & "result_count" & """" & ":0, " & """" & "results" & """" & ":[]}"
    
    If resultstring = emptyresult Then
        getNpi = "NPI not found"
        Exit Function
    End If
    
    searchstring = """" & "name" & """:"
    Namestart = InStr(1, resultstring, searchstring) + 9
    Nameend = InStr(Namestart, resultstring, ",") - Namestart - 1
    finalname = Mid(resultstring, Namestart, Nameend)
    getNpi = finalname
    Exit Function
errorhandler:
    getNpi = "Error in Request"
End Function


Sub test()
Debug.Print getNpi("1345319599")
End Sub

Excellent. Thanks LordLoki. That function worked in retrieving the name..Will mark as SOLVED.