Go Premium for a chance to win a PS4. Enter to Win


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.

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.


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. www.cwjobs.co.uk has deployed .NET (aspx extention) - the front page has a section called Contracts Of The Day - the program could check that section.

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

3. http://www.careersandjobsuk.com/latest-vacancies.html - 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

877 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