Solved

Check If A New Job Is Posted, Using VBA

Posted on 2011-03-02
9
263 Views
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.

Regards,

ref-IT
0
Comment
Question by:ref-IT
  • 5
  • 3
9 Comments
 
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

Chris
0
 
LVL 6

Assisted Solution

by:Eric Zwiekhorst
Eric Zwiekhorst earned 166 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

Eric
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 334 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.

Chris
Q-26857816.xls
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 2

Author Comment

by:ref-IT
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.

Regards,

ref-IT
0
 
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.

Chris
0
 
LVL 59

Assisted Solution

by:Chris Bottomley
Chris Bottomley earned 334 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:

Chris
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
                sh.Cells.Delete
                pageFormat sh, cel.Hyperlinks(1).address
            End If
        End If
    Next
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

0
 
LVL 2

Author Comment

by:ref-IT
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,

ref-IT
0
 
LVL 2

Author Closing Comment

by:ref-IT
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.

ref-IT
0
 
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

Chris
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

809 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