Improve company productivity with a Business Account.Sign Up


Check If A New Job Is Posted, Using VBA

Posted on 2011-03-02
Medium Priority
Last Modified: 2012-05-11
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.


Question by:ref-IT
  • 5
  • 3
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35016413
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


Assisted Solution

by:Eric Zwiekhorst
Eric Zwiekhorst earned 498 total points
ID: 35016415
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

LVL 59

Accepted Solution

Chris Bottomley earned 1002 total points
ID: 35016627
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.

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.


Author Comment

ID: 35018317
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.


LVL 59

Expert Comment

by:Chris Bottomley
ID: 35019086
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.

LVL 59

Assisted Solution

by:Chris Bottomley
Chris Bottomley earned 1002 total points
ID: 35019116
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


Author Comment

ID: 35025675
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,


Author Closing Comment

ID: 35025772
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.

LVL 59

Expert Comment

by:Chris Bottomley
ID: 35026046
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


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This following write-up describes a different way to copy Lotus Notes Calendar to Outlook. Along with this, we will also learn the reason behind this NSF to PST migration. Users can prefer different procedures as per their convenience.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question