Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf


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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

704 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