Download web page information via vba

Hello all,

Im stuck on using Internet explorer objects and how to approach the following issue.

I wish to see all the new planning applications that have been received for in past week here http://planning.bournemouth.go…Register/planappsrch.aspx, then extract out the information that comes out (if you click on the link it gives further information which I would like to capture such as name address etc). I had hoped that I could use the data from web function in excel, but that doesn't seem to give all the details (it cuts off at first ten).

Does anyone have any starters for ten, or an approach I can take or read about?

Re: Download web page information via vba

Hello,

Thanks for taking the time to respond, however I am not sure what this is actually doing? The output on sheet2 doesn't give me any details of the applications, nor the names of people which currently I would have to manually click into. I am hoping I can get steered into

• Populating the first web "search" page by just putting in the relevant date search (I know how to work out previous week date, I just don't know how to send it to web page). So if I try and put dates of June into received, I get 81 results
• The search results that come back I would like to get out the details (name, address, reason), which you can click on but not sure how to use vba to navigate through the different tabs on the webpage. So under the application column from the search results, I have to manually click then copy details from the main details and location tabs that come up, though id be interested to learn how all the tabs can be imported.

VBA with web objects I have little experience with, so some guidance would be terrific. Essentially once I have the data into the spreadsheet I will be able to do the rest.

Thanks in advance

Re: Download web page information via vba

Hello,

Is anybody able to direct me with how to approach this? Im assuming its possible but just need a push in the direction to take?

Re: Download web page information via vba

Thank you for helping. I am getting a run time "object required" error on this line, do I need to add in a reference?

x = Replace(IE.Document.GetElementByID("MainContent_grdResults_ctl00__" & i).OuterHTML, vbLf, ":")

Many many thanks for helping me! Also in the approach to this, is it then better to calculate how many pages or results it is, then individually program to open, copy and close each result until total number of results is reached? Given the way the web page is done it makes sense, but this is all new learning for me!

Re: Download web page information via vba

Ok, I understand the first part you gave me (go me) and I now understand how to get the name of the fields.

However the second part of code you gave me that errors I don't understand at all, and even a web search doesn't seem to help me so cant work out the problem! Sorry if I am being thick, but everything I have done in vba is self taught. Its eems you are using "regular expressions" but not sure how this works as I cant play with it?

Re: Download web page information via vba

Ok, thanks again.

I have Microsoft Internet Controls already ticked, but still giving me the run time error 424, am I doing something wrong? Once I get it pasting into excel I can then bodge round it, and will post back. Im sure my code historically wasn't "pure" but it got the job done, so quick and dirty isn't a problem!

Re: Download web page information via vba

Quote from S O;749971

hmmm.. not too sure what's causing that - have you tried going into IE->Tools->Internet Options->Security->Internet and uncheck "Enable Protected Mode"? (note you have to restart IE to take effect)

I wouldn't advise leaving this unticked, but I know it can cause problems with VBA automation.

Tried your suggestion, but still erroring on that line. Have Microsoft Internet Controls, Microsoft HTML Object Library and Microsoft VBScript Regular Expression 5.5 all ticked. Should I try an alternative HTML way of copying the results in to excel?

Re: Download web page information via vba

Still no joy.

What is odd is I retyped "ie.document" so that the menu would show, and it worked. Tried it again and same error, and retyping does nothing! Im well out of my depth now!

Also, I wanted to import the number of results from the search but it doesn't like this code.

Range("A1") = ie.Document.getElementById("contenttext").Value

in this example, I was hoping to return "Your search returned the following results (a total of 31).Click on an application number to view its case file." I would then extract the number 31, but have tried by value, innerhtml, innertext and errors.

Re: Download web page information via vba

Hello all,

This is getting to be a little too much for me! I have spent a lot of time researching, but a lot of it is going above my head, so help is appreciated! I had this code cobbled, but it doesn't seem to give me the results I want

Sub Grabber()
     
    Dim ie As Object
    Dim r As Integer, c As Integer, t As Integer
    Dim elemCollection As Object
    Dim form As Variant, button As Variant
    Dim html As HTMLDocument
    Dim Link As Object
    Dim ElementCol As Object
    Dim eRow As Long
    Dim myData As Variant
    Dim n As Long
     
    Set ie = CreateObject("InternetExplorer.Application")
     
'Wait until IE is done loading page
With ie
        .Visible = True
        .navigate "http://planning.bournemouth.gov.uk/RealTimeRegister/planappsrch.aspx"
        Do While ie.Busy Or ie.readyState <> 4
            DoEvents
        Loop
        
        With .Document
            .getElementById("MainContent_txtDateReceivedFrom").Value = CStr(ThisWorkbook.Sheets("Home").Cells(2, 11))
            .getElementById("MainContent_txtDateReceivedTo").Value = CStr(Date - 1)
            .getElementById("MainContent_btnSearch").Click
        End With
    End With
     
     
        Do While ie.Busy Or ie.readyState <> 4
            DoEvents
        Loop
         




Application.Wait Now + TimeSerial(0, 0, 5)
         
 Set Doc = ie.Document
      
        i = 1
        
'loop through all elements
For Each Element In ie.Document.all




           
'print the ones with a name together with corresponding inner text
If Len(Element.ID) <> 0 Then
Cells(i, 1) = Element.ID
Cells(i, 2) = Element.innerText
i = i + 1
End If
     
      
 Next Element
        
 ie.Quit
        
End Sub

Problems so far:-

1. The code doesn't always bring back the "results" and I cant work out why as the webpage should be loaded
2. IE.Quit doesn't close down the IE session.

Now heres the interesting thing. If I set IE.visible to false, the code seems to always bring the information back. Does anyone know why this is? I thought putting a do while meant that ie was completely loaded, but seems to be a timing issue? I know I will want to use the getelementsbyID approach, but I am getting all the elements simply to try and find out what is and isn't being loaded (originally it couldn't find any). However, I might go the root of copying everything, then deleting out the results I want.

The bigger picture is I want to click on all the results (so will have to look at how many results there are and find some code to load the next page), and for each result click on their further details and copy that information in.

I really hope someone can help as I'm close to crying! I did look at getting html directly but didn't understand it at all.

Re: Download web page information via vba

Also having trouble navigating to next page of search results