Using VBA to navigate a Web Page

[vince g]

I'm relatively new to VBA but have been sucessful if generating a number of home use apps to save myslef time and effort going forward over my old excel only versions. My latest effort requires that I get some table data from the web for a given tide station point. This I was able to do and my code worked fine extracting the required table data. The online tide data defaults to the current month. I now want VBA to navigate forward one or more months and to the re-used the code I have working to get future month tide data. The site provides tide data for a given tide station ( Scarborough in my case) for the whole month. What I cannot get working in this new area to me is forcing the webpage to move forward one month. I've tried for over a day and failed. The month can manually on the site be pushed forward by working the month dialogue which is in middle of the top banner or alternatively with the "Tides for Scarborough" table that is displayed lower down. Eith works to do teh same and change the month of the tides table I then extract. My code so far as below but just returns a bad method error. My knowledge here is weak so I may well be missing something fundamental. Any pointers most welcome.

My code now as here ( which includes the site url)

Sub GetTideDatafromWeb()

Dim Ie As InternetExplorer
Dim WebPage As New HTMLDocument
Dim Tidetable As String

Set Ie = New InternetExplorer
Ie.Visible = True

Tidetable = "https://tides4fishing.com/uk/england/scarborough"
     
Ie.navigate Tidetable

Do Until Ie.readyState = READYSTATE_COMPLETE
Loop

Dim sSpanid As String

Set WebPage = Ie.document
sSpanid = "tabla_mareas_mes_mes"

Dim SpanMonth As MSHTML.IHTMLSpanElement
Dim tInput As HTMLInputElement

Set SpanMonth = WebPage.getElementById(sSpanid)

Debug.Print "Initial month shown = "; SpanMonth.innerText

WebPage.getElementById(sSpanid).Value = "April"


End Sub

 

[Domenic]

See if this helps . . .

To click on the triangle to show the calendar . . .

WebPage.getElementById("boton_calendario_header_triangulo").Click

To select a month . . .

WebPage.getElementById("meses").selectedIndex = 3 '3 = April, since indexing starts at 0

To click on the OK button . . .

WebPage.getElementById("calendario_boton_aceptar").Click

To retrieve the month from the updated web page . . .

WebPage.getElementById("tabla_mareas_mes_mes").innerText

Note, though, it looks like when the information for a selected month is not available the web page gets updated with information for the month of June (I guess the last month with available information).

 

[vince g]

Domenic hi,

many thanks for taking the time to look at this problem and your reply. Very much appreciated. In short, your code did as I needed so that's great. What I have already in code on the tide table I believe I can wrap around this to get all tha I need ie tide data for each month going forward. I'll research more to better understand how/why your solution works.

The code runs fine so no complaints and much respect but I note there are significant pauses between the steps of opening the calendar and then selecting a month and then OK. 1-2 minutes to execute all steps while I watch it appear on my screen. Is there anthing more I can do to speed that up/manage the pauses? If not, that's fine - it works so geat. Thx again.

Vince

 

[Domenic]

Unfortunately, I think with Internet Explorer it's going to be slow. So you might want to consider using using Selenium instead. I don't have an experience with it, so hopefully someone here will be able to help. Otherwise, you should be able to search Google for plenty of examples.

With regards to managing pauses, add the following code after each click of the button . . .

    With Ie
        Do While .Busy Or .readyState <> READYSTATE_COMPLETE
            DoEvents
        Loop
    End With

 

[vince g]

Domenic hi,

thanks for the further suggestions. If I could have one final question. I've added your suggested code to my original to move forward 1 month. In short, if I run my original code it works fine and dumps the tide data out to a temp worksheet for the current month - where I then processes it further. However, when I add the code to make the website move forward one month and then run the same code following this my code fails with run-time error '70' Permission denied? I have waits in place so the page has refreshed to be the forward month. Do I need to add something further so that my code can access the refreshed page contents?

 

[Domenic]

The following code should serve as a starting point. Hopefully you'll be able to amend it as desired. Note that it will process the current month, along with the next one. Also, once December has been processed, it exits the sub.

Option Explicit

Sub GetTideDatafromWeb()

    Dim Tidetable As String
    Tidetable = "https://tides4fishing.com/uk/england/scarborough"
    
    Dim IE As InternetExplorer
    Set IE = New InternetExplorer
    
    With IE
        .Visible = True
        .navigate Tidetable
        Do While .Busy Or .readyState <> READYSTATE_COMPLETE
            DoEvents
        Loop
    End With
    
    Dim WebPage As New HTMLDocument
    Set WebPage = IE.document
        
    Dim sSpanid As String
    sSpanid = "tabla_mareas_mes_mes"
    
    Dim PageCount As Long
    PageCount = 1
    
    With WebPage
    
        Do
            'print the current month to the Immediate Window
            Debug.Print .getElementById(sSpanid).innerText
            
            'your code here to extract other data from current web page
            '
            '
            
            'if the month for the current web page is December, exit the sub
            If .getElementById(sSpanid).innerText = "December" Then Exit Sub
            
            'if the current month, along with the next one, have been processed, exit the sub
            If PageCount > 1 Then Exit Sub
            
            'show calendar
            .getElementById("boton_calendario_header_triangulo").Click
            
            'select next month
            With .getElementById("meses")
                .selectedIndex = .selectedIndex + 1
            End With
            
            'click OK
            .getElementById("calendario_boton_aceptar").Click
            
            'wait until page loads
            With IE
                Do While .Busy Or .readyState <> READYSTATE_COMPLETE
                    DoEvents
                Loop
            End With
            
            'increment page count
            PageCount = PageCount + 1
            
        Loop
        
    End With
    
    Set IE = Nothing
    Set WebPage = Nothing
    
End Sub

Hope this helps!