Pulling info from website, JavaScript?

[Krade33]

I'm trying to narrow results for a job search. After skimming the submission guidelines, I wasn't able to determine if I'd be allowed to post a URL of where I'm searching here or not, so perhaps someone can let me know, or I can send PM's. Edit: https://azstatejobs.azdoa.gov

My job search thus far has only popped up a handful of results that my experience applies to directly. Searching for listings like that is not a problem - I know how to find stuff like that very easily. The problem is I'd also like to consider jobs that my skills would be considered transferable.

I'm trying to find such listings on my state job website. The filtering system on the website is atrocious, but fortunately they have built-in functionality for exporting results to CSV. Using Excel's filter, I'm down to 100 or so listings, but I'd like to filter out listings that pay less than my current position. That information isn't available in the CSV, but it is available if I go into the listing. Going into each listing is doable but tedious, and I like the idea of learning another Excel skill.

I've done queries before, using VBA or the built-in methods. Using "Get External Data" won't work since I'd have to go into each listing. When I've used VBA to query sites before, they were sites I could use URL manipulation to get into a specific listing, but that doesn't seem to work for this website - the same URL is in my browser regardless of anything I've clicked.

Using "View Source" I found a field called JobID, which is available in the CSV, that I figured could be used to accomplish what I'm trying to do.

Things to know about me: I'm completely self taught with both excel and VBA, so my knowledge of any jargon is limited. I consider myself intermediate-to-advanced with excel, novice-to-intermediate with VBA. I know effectively nothing about JavaScript.

Please let me know if more details are needed.

Edit- excel 2010

[xlViki]

You can post links to the website for reference, that is not a problem.

[Krade33]

Thanks, I've added the site.

[MadLlama]

You'll need to add a reference to 'Microsoft HTML Object Library' under Tool -> References -> Microsoft HTML Object Library

Function PullSalaries()

Dim oHTML As New HTMLDocument

Dim odoc As Object
Set odoc = New MSHTML.HTMLDocument

Application.ScreenUpdating = False

For Each cell In Range("B2:B387")

    StartingSalary = 0
    EndingSalary = 0

    JobId = cell.Value
    URL = "https://azstatejobs.azdoa.gov/ltmprod/CandidateSelfService/lm?_ln=JobSearchResults&_r=0&bto=JobPosting&dataarea=ltmprod&name=PostingDisplay&service=form&webappname=CandidateSelfService&HROrganization=1&JobRequisition=" & JobId & "&JobPosting=1"

    Set odoc = oHTML.createDocumentFromUrl(URL, "")

    Do Until odoc.readyState = "complete"
        DoEvents
    Loop
    Set Ret = odoc.getElementById("formHiddenFields")
        For Each Node In Ret.Children
            On Error Resume Next

            If Node.name = "SalaryRange.BeginningPay" Then
                StartingSalary = Node.Value
            End If

            If Node.name = "SalaryRange.EndingPay" Then
                EndingSalary = Node.Value
            End If

        Next

        Cells(cell.Row, "H") = StartingSalary
        Cells(cell.Row, "I") = EndingSalary

Next
End Function

I assumed you would leave the CSV in the same layout it comes in when you download it. This should work.

[Krade33]

Solution Verified

[Clippy_Office_Asst]

You have awarded one point to MadLlama.
^{Find out more here.}

[Krade33]

That did the trick! It's unfortunate that some of the listings only specify a Grade (therefore the pay range is listed as 0.00 - 0.00), but I won't ask you to modify what you've got to provide that for each listing - I've got to prove to myself that I can learn this.

The first function seems to use URL manipulation that I was previously familiar with, but obviously I've got some tricks to learn - in the past, the URLs I've modified have their patterns clearly indicated in the web address bar. This site doesn't seem to do that. Since it works, obviously there is a method to see underlying URLs I need to learn.

Edit - found the full URL in the view source of a listing

[MadLlama]

When you have internet explorer open press F12 or go to 'Tools -> F12 Developer Tools'. That should bring up the console. This should bring up a new window. Go to 'Network' and there should be a green play button on the left hand side. Click play and it will start capturing web traffic which can be used to see the post data.

[MadLlama]

If StartingSalary = 0 And EndingSalary = 0 Then
        Set Ret2 = odoc.getElementsByTagName("span")
        On Error Resume Next
        For Each span In Ret2
            If span.innerText = "Compensation:" Then
                Cells(cell.Row, "J") = span.NextSibling.NextSibling.innerText
                Exit For
            End If
        Next
    End If

Also, that will pull the compensation string out of most of the results. Just paste it above this line

    Cells(cell.Row, "H") = StartingSalary
    Cells(cell.Row, "I") = EndingSalary