VBA code to loop through link, click them and close browser

Question

I want my code to perform this set of steps:

1. get the URL from a cell
2. open the URL
3. wait some seconds to let the page load
4. click a button on the page to create a DB object
5. wait some second to let the page load
6. close the page
7. update the status bar to show progress

Opening and closing IE at every instance of the loop is needed to avoid clogging IE with too many tabs. In the sheet there might by up to 1000 URL's.

This is the loop I have written

For i = 14 To 13 + Count
    'get the URL
    URL = Range("F" & i)
    'Open IE instance
    Set IE = CreateObject("InternetExplorer.Application")
    'navigate to the URL
    With IE
        .Visible = True
        .Navigate URL
    End With
    'wait that the page is loaded
    Application.Wait (Now() + TimeValue("0:00:4"))
    'click on the create object button
    Set Tags = IE.Document.GetElementsByTagname("Input")
    For Each tagx In Tags
        If tagx.alt = "Create object" Then
            tagx.Click
        End If
    Next
    'wait the page to be loaded
    Application.Wait (Now() + TimeValue("0:00:10"))
    'close the tab
    IE.Quit
    'update progess bar
    Application.StatusBar = "Progress: " & i - 13 & " of " & Count & ": " & Format((i - 13) / Count, "0%")
Next i

when I check the code step by step I get this error

on the set Tags line, and I have no clue how to fix it.

I have tried recording a macro to see how VBA sees the action of clicking the button, but that action is not recorded at all.

Answer 1

Couple things that might be causing your issues:

1. I would move the initialization and closing of your IE outside of the loop- leaving them inside just makes IE open and close over and over but a single instance of IE can be used over and over for the loop.

2. Replace your wait functions with While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE: Wend This will make the application wait until the pages are completely loaded before proceeding.

3. Wasn't exactly clear to me what you were doing by clicking on the element, but I believe this is probably where your error is coming from- after you click on the link the loop continues but IE has navigated to a different page so the HTML has changed- in my experience this will cause element collections to reset, which would break your loop.

Below are two versions of the code I would use- version 1 clicks on the first element that meets the If condition and then does nothing else. If there were other links that met the If condition they would not get clicked on because the loop is broken once the first element found and clicked. See below:

Sub version1()
Dim URL As String
Dim IE As InternetExplorer
Set IE = New InternetExplorer

For i = 14 To 13 + Count
    URL = Range("F" & i)
    With IE
        .Visible = True
        .Navigate URL
        While .Busy Or .ReadyState <> READYSTATE_COMPLETE: Wend
    End With
    Set tags = IE.document.getElementsByTagName("Input")
    For Each tagx In tags
        If tagx.alt = "Create object" Then
            tagx.Click
            Exit For
        End If
    Next
    While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE: Wend
    Application.StatusBar = "Progress: " & i - 13 & " of " & Count & ": " _
        & Format((i - 13) / Count, "0%")
Next i
IE.Quit
Set IE = Nothing
End Sub

Version 2 uses a collection to store all of the elements that meet the if condition, and then at the end goes through and clicks on each one... see below:

Sub version2()
Dim URL As String
Dim IE As InternetExplorer
Dim links As Collection
Set IE = New InternetExplorer

For i = 14 To 13 + Count
    Set links = New Collection
    URL = Range("F" & i)
    With IE
        .Visible = True
        .Navigate URL
        While .Busy Or .ReadyState <> READYSTATE_COMPLETE: Wend
    End With
    Set tags = IE.document.getElementsByTagName("Input")
    For Each tagx In tags
        If tagx.alt = "Create object" Then
            links.Add tagx
        End If
    Next

    For counter = 1 To links.Count
        links.Item(counter).Click
        While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE: Wend
    Next
    Set links = Nothing
    Application.StatusBar = "Progress: " & i - 13 & " of " & Count _
        & ": " & Format((i - 13) / Count, "0%")
Next i
IE.Quit
Set IE = Nothing
End Sub

Depending on your intentions and what you were trying to do one of the two versions should be suitable for you.

Hope this helps