# Excel Macro to open then close a URL



## firstshot (Jun 12, 2012)

I have a spreadsheet with a column of 5,000 URLs. I'm using a macro to open each of the url's, flag the spreadsheet as "Good URL" or "Bad URL" and then hopefully close the URL that was opened.

I'm using the following to open the URLs:


```
Dim HLINK As String
HLINK = Selection
ActiveWorkbook.FollowHyperlink Address:=HLINK, NewWindow:=True
```
Now that I've got the URL open, what is the macro code to close the webpage once I'm done with it?

Thanks
firstshot


----------



## Jimmy the Hand (Jul 28, 2006)

Excel does not provide the way to close the browser as easily as it was opened. You would need to use Windows API functions to identify the browser window and close it. It's a tricky business.

I offer you a solution with a different approach. In only works with Internet Explorer, though.

```
Dim IE As Object, HLINK As String
    HLINK = Selection.Value
    Set IE = CreateObject("InternetExplorer.Application")
    IE.navigate HLINK
    IE.Visible = True
    While (IE.Busy) Or (IE.ReadyState <> 4)
        'wait until ready
    Wend
    IE.Quit
```
Of course, in case you want to open 5000 URLs, it would be a waste of time to close the borwser after each URL, then open a new one. The browser object in the above code is reusable as many times as you need:


```
Dim IE As Object, HLINK As String, c As Range
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True

    For Each c In Selection.Cells
        HLINK = c.Value
        IE.navigate HLINK
        While (IE.Busy) Or (IE.ReadyState <> 4)
            'wait until ready
        Wend
    Next

    IE.Quit
```
Jimmy


----------



## Jimmy the Hand (Jul 28, 2006)

On second thought, your code could be just as good as mine, if you set NewWindow to False? 
That way the browser needs to be closed only once: when you are finished with the whole job.


----------



## firstshot (Jun 12, 2012)

Hello Jimmy

Thanks so much for your input. I've decided to just set Newwindow to false as you suggested, as it would be a lot less overhead instead of opening and closing IE over and over.

Thanks again
firstshot


----------



## firstshot (Jun 12, 2012)

Hello Jimmy

I changed the follow HTML code to "NewWindow:=False" but it is still opening a new window for each URL opened. (actual code at bottom of post)

Here are some sample URL's and code results.
*Hyperlinks**Code - Code Results*
http://www.google.com - Good URL
http://www.youtube.com - Good URL
http://www.basspro.com - Good URL

Any idea why it is still opening a new IE window for each URL?

Thanks
firstshot

Resulting IE window:










```
Sub HYPERLINK_Open()

    Dim HLINK As String
    Dim CurrRow As Long
    Dim OldHlink As String
    Dim TestValue As String
    OldHlink = "xxxx"
' Application.ScreenUpdating = False

Check_URL:
    Do
        HLINK = Selection
        CurrRow = ActiveCell.Row
        Application.StatusBar = "Processiong row " & CurrRow
        If HLINK = OldHlink Then GoTo DupURL

    On Error GoTo errhandler
        ActiveWorkbook.FollowHyperlink Address:=HLINK, NewWindow:=False
        ActiveCell.Offset(0, 1).Value = "Good URL"
        TestValue = "Good URL"
        ActiveCell.Offset(1, 0).Range("A1").Select
        If IsEmpty(ActiveCell) Then Exit Sub
        OldHlink = HLINK

    Loop
errhandler:
        ActiveCell.Offset(0, 1).Value = "Error"
        TestValue = "Error"
        ActiveCell.Offset(1, 0).Range("A1").Select
        If IsEmpty(ActiveCell) Then Exit Sub
        OldHlink = HLINK
        Resume Check_URL

DupURL:
        ActiveCell.Offset(0, 1).Value = TestValue
        ActiveCell.Offset(1, 0).Range("A1").Select
        If IsEmpty(ActiveCell) Then Exit Sub
        OldHlink = HLINK
        GoTo Check_URL

End Sub
```


----------



## Jimmy the Hand (Jul 28, 2006)

Sorry, I thought NewWindow=False would do the trick. If it doesn't then I can't help on this path.
I suggest we take the other path. Here's a code that hopefully does what I think you want.
I took your code and also took the liberty to tailor it a bit. 
I must add, however, that using Selection and ActiveCell is bad practice.


```
Sub HYPERLINK_Open()
 
    Dim HLINK As String
    Dim CurrRow As Long
    Dim OldHlink As String
    Dim TestValue As String
    Dim IE As Object
    ' Application.ScreenUpdating = False
    
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    OldHlink = "xxxx"
    Do
        HLINK = Selection
        CurrRow = ActiveCell.Row
        Application.StatusBar = "Processiong row " & CurrRow
        If HLINK = OldHlink Then
            TestValue = "Duplicate"
        Else
            OldHlink = HLINK
            IE.navigate HLINK
            While (IE.Busy) Or (IE.ReadyState <> 4)
                'wait until ready
            Wend
            If InStr(LCase(IE.document.URL), "error") > 0 Then
                TestValue = "Error"
            Else
                TestValue = "Good URL"
            End If
        End If
        ActiveCell.Offset(0, 1).Value = TestValue
        ActiveCell.Offset(1).Activate
        If IsEmpty(ActiveCell) Then Exit Do
    Loop
    IE.Quit
End Sub
```
Jimmy


----------

