Check If A New Job Is Posted, Using VBA

Hello Experts,

I wanted to query if it was possible to check on Job Websites if a New Job had been posted using VBA - in Excel.

Ideally, my Excel Worksheet will have URLs of Job Sites listed on every row. The VBA code has to go through them step by step and check if a new jobs has been posted. If a new job has been posted, then it needs to extract some information from that newly posted job - like Job Name (e.g "Programmer" or "Civil Engineer" etc) and other information and put it on another worksheet in the same workbook.

Can the above be done using VBA?

Many Thanks in Advance and any pointer will be Highly Appreciated.


Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Chris BottomleyConnect With a Mentor Commented:
As an example see the attachjed workbook.

It will access any sites you enter into the page column 2.  The returned data appears as a sheet ... the problem then is to process each sheet for the required data.

Chris BottomleyCommented:
Possibly.  What and how will tend to vary with the technologies deployed by the websites so will be specific to each site and require access to evaluate / test.

So the short answer is yes if you can provide access to the relevant sites

Eric ZwiekhorstConnect With a Mentor SAP Business ConsultantCommented:
Dear REF,

you can make a web querry to the websites holding this information.
If the newest are visible on the first site opening than it should have what you wanted.
You can check if there are diferences to a copy of the data...

Kind regards

Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

ref-ITAuthor Commented:
Hello Chris and Eric,

Thank you very much for your prompt responses.

Chris, I downloaded the file, and ran the assigned coded macro evaluateLink. The results are good but a little haphardous or jumbled up for a program to orderly evaluate what information goes where. Lets say, we take three different job sites that have deployed three popular technologies. Say e.g:

1. has deployed .NET (aspx extention) - the front page has a section called Contracts Of The Day - the program could check that section.

2. uses Common Gateway Interphase (cgi)  - There's a section on the top of the page that scrolls new jobs

3. - Im not sure what technology this site uses but the section here highlights new IT Jobs.

Also, I was wondering it the code in the excel worksheet was an application of web queries that Eric was mentioning (Please do excuse me if you find my question Naive - I only want to learn more)

Many Many Thanks to both of you in advance.


Chris BottomleyCommented:
Hmmm thought i'd already posted an answer back ... obviously not!

Have looked anyway at these sites:

On a quick look the returned data is useless, you would need to traverse the sites setting appropriate data into the boxes. This is generally viable though it will be dependant as stated on there being no change to the structure and the site implementation.  This is therefore not particularly practicable in my view so my answer here is probably doable technically but not practicable at the volunteer level i'm afraid.

Out of interest I cant say of course if this is the mechanism Eric was referring to ... it is merely one of the tools at our disposal ... but unless the info can all be found in a directly addressable page this particular mechanism is of little help, and the other mechanisms are more implementation specific so for a request of this magnitude I would find it too much hence my comment.

This is not to say that some of the others can't do it easier or are unwilling to invest the necessary time
and skills so don't lose hope someone else may take up the ball ... and if in a few days no-one has then try a request for attention to the mods to bring fresh eyes here.

Chris BottomleyConnect With a Mentor Commented:
BTW there are some bugs in that script re the naming ... but as the mechanism is flawed I haven't uploaded it rather here is the correction to the workbook sheet code:

Sub evaluateLink()
Dim rng As Range
Dim cel As Range
Dim sh As Worksheet
Dim arr As Variant
Dim rw As Long

    Set rng = Intersect(Sheets("getpage").UsedRange, Sheets("getpage").Columns(2))
    If rng Is Nothing Then Exit Sub
    For Each cel In rng
        If cel <> "" Then
            If cel.Hyperlinks.Count = 1 Then
                On Error Resume Next
                Set sh = Nothing
                Set sh = Sheets(cel.Offset(0, -1).Value)
                On Error GoTo 0
                If sh Is Nothing Then
                    Set sh = Sheets.Add(after:=Sheets(Sheets.Count))
                    If cel.Offset(0, -1) <> "" Then sh.Name = cel.Offset(0, -1)
                End If
                pageFormat sh, cel.Hyperlinks(1).address
            End If
        End If
End Sub

Sub pageFormat(sh As Worksheet, strURL As String)

    With sh.QueryTables.Add(Connection:="URL;" & strURL, _
        Destination:=Range("'" & sh.Name & "'!$A$1"))
        .Name = sh.Name
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Open in new window

ref-ITAuthor Commented:
Hey Chris,

Thank you very much for your comments and help.

I do understand the magnitude of assistance I may be asking for and that it's not viable to give a solution here. However, you have at least laid the ground for me for further research on that note - and your code was certainly helpful.

I'll accept your last comment as a solution with the workbook one as an assisted solution.

Many Thanks and Regards,

ref-ITAuthor Commented:
The code managed to solve the problem but the result was not as orderly as for a program to comprehend where to expect what data.

However, I would not blame a programmer for that as its something cannot be comprehended easily.

Chris BottomleyCommented:
There is no problem with us working with you to get a structure out of the chaos ... but as I hopefully suggested the first step will be to make sure the info you need is presented on the page that is processed.

If you want to start such analysis then ask a related question and provide one of the pages with relevant info returned and what you want to see from it and we can try to process it.

That aside thank you for the prompt closure

All Courses

From novice to tech pro — start learning today.