Getting text in <h1> tag

[Mattestion]

Hi. I'm using Excel 2007 on Vista SP2. I'm writing a sub to submit an address from Excel into Canada Post website to retrieve the postal code. For now I have hard-coded the address but I will change it to variables later. So far I have been able to enter the address, click the find button, and get the result page. The trouble I'm having is getting the postal code. When I look at the source, it is found in a tag. So I thought using getElementsByTagName would work but it doesn't (or at least the way I used it). I've also tried using body.innerText or body.innerHTML and then text extraction but couldn't do it. What do I need to do or am I trying to do something that can't be done? P.S. If anyone needs to see the web page source, just run the sub to get the results page then use your browser to view its source.

Here is my code so far:

Sub SEARCH()
    Dim ie As Object
    Set ie = CreateObject("internetexplorer.application")
    ie.Visible = True
    
    ie.navigate "http://www.canadapost.ca/cpotools/apps/fpc/personal/findByCity?execution=e1s1"
    
    Do
        If ie.readyState = 4 Then
            ie.Visible = False
            Exit Do
        Else
            DoEvents
        End If
    Loop
    ie.Visible = True
    
    ie.document.getElementById("fpcByAdvancedSearch:fpcSearch:streetNumber").Value = "2650"
    ie.document.getElementById("streetName").Value = "Thimens"
    ie.document.getElementById("city").Value = "Saint-Laurent"
    ie.document.getElementById("fpcByAdvancedSearch:fpcSearch:province").Value = "QC"
    
    Set tags = ie.document.getElementsByTagName("Input")

    For Each tagx In tags
        If tagx.alt = "Find" Then
            tagx.Click
        End If
    Next
    
    Do
        If ie.readyState = 4 Then
            ie.Visible = False
            Exit Do
        Else
            DoEvents
        End If
    Loop
    ie.Visible = True  
    
End Sub

 

[Norie]

Is it just the postcode you want?

It's quite easy to get the full address, including the postcode.

Is that any use?

 

[Mattestion]

I already have the address but I don't have the postal code. That's what I'm looking for is the postal code.

 

[Mattestion]

On second thought, I don't mind getting the address with the postal code as long as there is a way to extract the postal code.

 

[Norie]

Try this.

It's not an ideal solution, it kind of relies on there only being a few H1 elements.

Sub SEARCH()
Dim ie As Object
Dim doc As Object
Dim fnd As Object
 
    Set ie = CreateObject("internetexplorer.application")

    ie.Visible = True
 
    ie.navigate "http://www.canadapost.ca/cpotools/apps/fpc/personal/findByCity?execution=e1s1"
 
    Do
        If ie.ReadyState = 4 Then
            Exit Do
        Else
            DoEvents
        End If
    Loop
 
    Set doc = ie.document
 
    doc.getelementbyid("fpcByAdvancedSearch:fpcSearch:streetNumber").Value = "2650"

    doc.getelementbyid("streetName").Value = "Thimens"

    doc.getelementbyid("city").Value = "Saint-Laurent"

    doc.getelementbyid("fpcByAdvancedSearch:fpcSearch:province").Value = "QC"
 
    Set fnd = doc.getelementbyid("fpcByAdvancedSearch:fpcSearch:fpc_psn_common_pcs_find_1")
 
    fnd.Click
 
    Do
        If ie.ReadyState = 4 Then
            Exit Do
        Else
            DoEvents
        End If
    Loop
 
    Do While doc.ReadyState <> "complete": DoEvents: Loop
           
    Set doc = ie.document
    
    Set x = doc.getelementsbytagname("h1")
 
    MsgBox x(2).innertext
 
    ie.Visible = True
 
End Sub

I've added another loop to make sure the document has completed loading.

 

[Mattestion]

Thank you so much! I've been banging my head for 2 days now trying to figure it out. I'm new at using VBA and DOM so I will study this code.

 

[Mattestion]

One more question. How do I get the address in my excel sheet into the sub and return the postal code to the sheet? I was thinking of using a function to get the values, pass it to the sub, then have the sub pass the postal code to the function.

 

[Norie]

You could just replace the hardcoded address in the code with references to the cells with the address on the worksheet.

Then you can just do the reverse for the postcode.

I can't remember the code offhand but to get the addresses parts you could use something like this.

strStreetNo = Range("A2").Value
 
strAddress  = Range("B2").Value
 
strCity = Range("C2").Value
 
strProvince = Range("D2").Value

So you would have 'Saint-Laurent' in C2 and 'QC' in D2, can't remember what the street no and address where, but you get the idea.

If you want to do this for multiple addresses just arrange the data in columns like that.

eg Column A - Street No, Column B - Address...

To return the postcode to the worksheet instead of MsgBox you could try something like this.

Range("E2").Value = x.Items(2).innerText

So that would be in column D of the same row the address is on.

One thing if you are going to loop, don't write the code so you go through the whole process of opening IE, navigating etc.

You should only need to do that once, reusing the same instance of IE to search for all the addresses.

 

[Mattestion]

OK but how do I get the sub to run? Do I have to open the VB editor and click run or is there another way? Sorry I'm getting a bit away from the original question.

 

[Norie]

Sorry I don't understand.

How/when do you want the code to run or be triggered?