Fetch Data from a web page to Excel using VBA

Question

I am trying to pull data from the following web page.

https://pro.calnea.com/login

Pass: calnea1

Once logged in hoover over "Pro Services" and click "Comps Search (Photo)". I have typed a post code in and ticked a couple of properties already which should put them in the Shortlist. To access the short list go to the bottom on the page and on the right, there is a button which says "View Shortlist", click that. Now you see the selected properties and I want to pull each piece of data for each property, for example, cell A1 = Address, A2 = Last Sales Price, A3 = Last Sales Date etc all the way to Status. Then the next property on the next line so B1 = Address etc. If possible I want to get the image URL as well.

I am not sure of the best way around this as there is a log in however I am remaining logged in on the browser so I assume this isnt an issue?

Below is what I have so far but unfortunately I am having no luck and help would be MUCH appreciated! :)

Sub test()
Dim eRow As Long
Dim ele As Object
Set sht = Sheets("Sheet1")
RowCount = 1
sht.Range("A" & RowCount) = "Address"
sht.Range("B" & RowCount) = "Last Sales Price"
sht.Range("C" & RowCount) = "Last Sales Date"
sht.Range("D" & RowCount) = "Property Type"

eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Set objIE = CreateObject("InternetExplorer.Application")


With objIE
.Visible = True
.navigate "http://pro.calnea.com/client/cmp_shortlist/bs6?Require_EA=false&SearchMode=CMP"
Do While .Busy Or _
.readyState <> 4
DoEvents
Loop
Set what = .document.getElementsByName("q")
what.Item(0).Value = Address
Set Address = .document.getElementsByName("where")
Address.Item(0).Value = Last Sales Price
.document.getElementById("View Shortlist").Click
Do While .Busy Or _
.readyState <> 4
DoEvents
Loop
For Each ele In .document.all
Select Case ele.classname
Case "Result"
RowCount = RowCount + 1
Case "Title"
sht.Range("A" & RowCount) = ele.innertext
Case "Company"
sht.Range("B" & RowCount) = ele.innertext
Case "Location"
sht.Range("C" & RowCount) = ele.innertext
Case "Description"
sht.Range("D" & RowCount) = ele.innertext
End Select
Next ele
End With
Macro1
Set objIE = Nothing
End Sub

very bad practice to post your info to login on this site, I have removed it for your benefit. — Sorceri
– Sorceri, Commented Nov 14, 2013 at 18:04
@Sorceri the fact that you mentioned you removed it,now someone can see the edit history not that i would use someone elses password but just saying its not totally removed. — Foo Bar User
– Foo Bar User, Commented Nov 14, 2013 at 21:13
Its removed as much as I can make it which is all I can do to help them. no one should ever post their info to a public site. And if you wouldnt have posted the link it could have remained kind of hidden but now anyone can go look thanks to your link! — Sorceri
– Sorceri, Commented Nov 14, 2013 at 21:22
@Sorceri Hi Sorceri Thank you for your concern with regards to my log in details however I created that as a temporary account so people could log in — user2964645
– user2964645, Commented Nov 15, 2013 at 10:49

Community · Accepted Answer · 2017-05-23 12:25:58Z

1

I seen the link from Fetch data from zoopla.co.uk, I just corrected some VBA syntax error, please check this:

Sub sofFetchDataFromWebPage()
  Dim RowCount, eRow As Long
  Dim sht, ele As Object, what, Address
  Dim objIE

'
  Set sht = Sheets("Sheet1")
'
' Set sht = ActiveSheet

  RowCount = 1
  sht.Range("A" & RowCount) = "Address"
  sht.Range("B" & RowCount) = "Last Sales Price"
  sht.Range("C" & RowCount) = "Last Sales Date"
  sht.Range("D" & RowCount) = "Property Type"

  eRow = sht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

  Set objIE = CreateObject("InternetExplorer.Application")

  With objIE
    .Visible = True
    .Navigate "http://pro.calnea.com/client/cmp_shortlist/bs6?Require_EA=false&SearchMode=CMP"
    Do While .Busy Or .readyState <> 4
      DoEvents
    Loop
    Set what = .document.getElementsByName("q")
    what.Item(0).Value = "Address"
    Set Address = .document.getElementsByName("where")
    Address.Item(0).Value = "Last Sales Price"
    .document.getElementById("View Shortlist").Click
    Do While .Busy Or .readyState <> 4
      DoEvents
    Loop
    For Each ele In .document.all
      Select Case ele.classname
        Case "Result"
          RowCount = RowCount + 1
        Case "Title"
         sht.Range("A" & RowCount) = ele.innertext
        Case "Company"
          sht.Range("B" & RowCount) = ele.innertext
        Case "Location"
          sht.Range("C" & RowCount) = ele.innertext
        Case "Description"
          sht.Range("D" & RowCount) = ele.innertext
      End Select
    Next
  End With
  Set objIE = Nothing
End Sub

As you has logged in in another IE Window, you may not need do login here again.

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered Nov 14, 2013 at 19:21

jacouh

8,8055 gold badges36 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2964645 Over a year ago

Thank you for taking a look at the code. I have high lighted the bits of information which I need to take for each property and paste on each line of Excel however the code is not working. I have only asked it to get four pieces of data as of yet and this is to keep it simple until I have figured it out, however the end result is to get all the information for each property on each Row. Any help with this would be great. Thank you link

jacouh Over a year ago

You shall study returned HTML document structure for see classnames, tagnames, attributes to determine the strategy to fetch data.

Collectives™ on Stack Overflow

Fetch Data from a web page to Excel using VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related