Extract Data from Web Page without Tags

I need to extract data from a web page using VBA.  I have everything working but when I get to the webpage, there is only a single number returned and I am not sure how to pull that number into a variable.  There are no tags to reference.

This is an example that I am working with.

https://192.168.1.242/act_pricerequest_count.cfm?LoanDate1=4/15/2018&LoanDate2=5/14/2018

I simply use IE.navigate "https://192.168.1.242/act_pricerequest_count.cfm?LoanDate1=4/15/2018&LoanDate2=5/14/2018" to get to this page.  I just need to know how to get the number returned, 62, into a variable of rngAutomated.
rsburgeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
No one outside your organisation can navigate to your posted internal IP address.
0
rsburgeAuthor Commented:
Sorry about that.

https://locks.platinumez.com/act_pricerequest_count.cfm?LoanDate1=4/15/2018&LoanDate2=5/14/2018

This number returned is 6817 and is just the number.
0
Gustav BrockCIOCommented:
You can set a reference to "Microsoft XML, v6.0", then run something like this:

Public Function RetrieveDataResponse(ByVal ServiceUrl As String) As String

    ' ServiceUrl is expected to have URL encoded parameters.
    
    ' Fixed constants.
    Const Async             As Boolean = False
    Const StatusOk          As Integer = 200
    Const StatusNotFound    As Integer = 404
    
    Dim XmlHttp             As XMLHTTP60
    Dim ResponseText    As String
       
    Set XmlHttp = New XMLHTTP60
     
    XmlHttp.Open "GET", ServiceUrl, Async
    XmlHttp.send

    ResponseText = XmlHttp.ResponseText
    Select Case XmlHttp.status
        Case StatusOk
            ' Result = True
        Case StatusNotFound
            ' Result = False
    End Select
    
    RetrieveDataResponse = ResponseText

Exit_RetrieveDataResponse:
    Set XmlHttp = Nothing
    Exit Function

Err_RetrieveDataResponse:
    MsgBox "Error" & Str(Err.Number) & ": " & Err.Description, vbCritical + vbOKOnly, "Web Service Error"
    Resume Exit_RetrieveDataResponse

End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

rsburgeAuthor Commented:
I added this to my project and added Microsoft XML 6.0 to the references, but when I try to run it, it brings up the box that asks which macro I want to run.

I must be doing something wrong.
0
Gustav BrockCIOCommented:
Can't tell, but it is air code - a simplified version of what I am using here:

VBA.CVRAPI

in the function RetrieveDataResponse  to pull some Json data - these are effectively just one long string.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I added this to my project and added Microsoft XML 6.0 to the references, but when I try to run it, it brings up the box that asks which macro I want to run.

This a Function with argument and should be called from another macro.
It seems, you placed the cursor inside the function and tried to run it.
When calling this function from another macro, you need to pass target URL as a parameter.
e.g. the following code calls this function.

Sub CallFunction()
Dim URL As String
Dim WebStr As String
URL = "https://locks.platinumez.com/act_pricerequest_count.cfm?LoanDate1=4/15/2018&LoanDate2=5/14/2018"
WebStr = RetrieveDataResponse(URL)
MsgBox WebStr
End Sub

Open in new window

0
rsburgeAuthor Commented:
I apologize for the delay, I was out of the office doing training and was unable to get back to this until today.  This is working exactly as needed now.  Thank you very much!
0
Gustav BrockCIOCommented:
No problem. Have a nice weekend!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.