0 votes
in VBA by Beginner (5 points)
I am working on how to post my data to a website JungleScout and let it calculate the estimated sales according to the sales rank and then return the result back to another column in Excel.

My code is like this:

Function JSESTIMATOR(salesRank As Integer)
    Dim url As String
    Dim winHttp As Object
    Dim scriptControl As Object
    Dim oData As Object
    Dim estSalesResult As Integer
    
    On Error GoTo error:
        url = "https://api.junglescout.com/api/v1/sales_estimator?rank=" & salesRank & "&category=Books&store=us"
        
        Set winHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
        winHttp.Open "GET", url, False
        winHttp.setRequestHeader "Content-Type", "application/json"
        winHttp.setRequestHeader "Accept", "application/json, text/javascript, */*; q=0.01"
        winHttp.setRequestHeader "Origin", "https://www.junglescout.com"
        winHttp.setRequestHeader "Referer", "https://www.junglescout.com/"
        winHttp.Send
        
        If winHttp.Status = 200 Then
            Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
            scriptControl.Language = "JScript"

            Set oData = scriptControl.Eval("(" + winHttp.responsetext + ")")
            estSalesResult = oData.estSalesResult
        Else
            estSalesResult = 0
        End If
        
        JSESTIMATOR = estSalesResult
error:
    Debug.Print "Error " & Err.Number; ":" & Err.Description
End Function

However, The result is #NAME?.

1 Answer

0 votes
by Expert (916 points)
selected by
 
Best answer

Try this

Sub Test_JSESTIMATOR_UDF()
    Debug.Print JSESTIMATOR(5)
End Sub

Function JSESTIMATOR(salesRank As Integer)
    Dim estSalesResult, winHttp As Object, oScriptControl As Object, oData As Object, sURL As String
    On Error GoTo Skipper:
    sURL = "https://api.junglescout.com/api/v1/sales_estimator?rank=" & salesRank & "&category=Books&store=us"
    Set winHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
    With winHttp
        .Open "GET", sURL, False
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Accept", "application/json, text/javascript, */*; q=0.01"
        .setRequestHeader "Origin", "https://www.junglescout.com"
        .setRequestHeader "Referer", "https://www.junglescout.com/"
        .send
        If .Status = 200 Then
            Set oScriptControl = CreateObject("MSScriptControl.ScriptControl")
            oScriptControl.Language = "JScript"
            Set oData = oScriptControl.Eval("(" + winHttp.responseText + ")")
            estSalesResult = oData.estSalesResult
        Else
            estSalesResult = 0
        End If
        JSESTIMATOR = estSalesResult
    End With
    Exit Function
Skipper:
    Debug.Print "Error " & Err.Number; ":" & Err.Description
End Function

 

by Beginner (5 points)
+1
Thank you so much! I appreciate your help.
by Expert (916 points)
You're welcome. Glad I can offer some help.
by Beginner (5 points)
I ran it with an number of data (About 11326 sales ranks) and got 0 for all of them, which is strange because a few minutes I ran it with 1 sales rank number, it returns a correct result. Could you please explain why and if there is a solution to it? Thanks!
by Expert (916 points)
How do you loop those 11326 sales ranks ..? Maybe the website block the massive requests. I am not sure.

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...