About Visual Basic reader George Maimonides wanted to know how to automatically open URL's in an Excel spreadsheet and copy information from them. He noted that his application did have some characteristics that made the problem easier, "When I open the URL's in Excel, the information that I want to extract is always located in the same cell. For example. Sheet3 cell G13 will always display the customer name." George also noted that his application might reference thousands of web pages, so performance was an issue!

Also, Manny asked about using an HTTPS URL instead of HTTP. The answer to that is found in a later addition to this article that you can find here.

Note: Examples pictured have been developed in VBA for Excel 2003. This is implemented using the VB 6.3 level of Visual Basic rather than VB.NET. Some testing was done to ensure that the code is compatible with VBA for Excel 2000 but it's possible that a few compatibility bugs might remain.

Sub Macro1()
    Range("A2").Select
    Selection.Hyperlinks(1).Follow _
        NewWindow:=False, AddHistory:=True
    Sheets("ResultsReports").Select
    Range("A2").Select
    ActiveSheet.Paste
End Sub

Looks great! Except that the operation of selecting the text from the web page, of course, did not get recorded because Excel can only record spreadsheet operations. So, in general, there isn't a way to find the data directly on the web page. (As we will see in a later installment, there actually IS. For one thing, you could load Internet Explorer as an object and search the text on the page for what you want. An even better way is called WebZinc. But for the time being, we'll pretend that we don't know anything about these methods.)

We need a better way!

As it turns out, if you open a web page as a spreadsheet, Excel will automatically import the web page into a local workbook and then you can address the content ... and even record the operations! Let's try that instead.

The first trick here is to remember to copy the name of the URL into the clipboard before trying to open it. Excel doesn't seem to have a direct way to open the webpage as a file. But if you copy it to the clipboard first, then you can paste it into the Open dialog in Excel.

The second trick is to realize that Excel has automatically copied the article title that we're looking for into a merged cell! Excel does this by basically copying the format of the tables in the HTML document. So we have to unmerge the cell and reselect the cell with the title actually in it to copy it.

Once these minor matter have been taken care of, we get this result:

Sub Macro1()
    Range("A2").Select
    Selection.Copy
    Application.CutCopyMode = False
    Workbooks.Open Filename:= _
        "http://visualbasic.about.com/library/bldykblkscpa.htm"
    Range("B10:C10").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 1
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.UnMerge
    Range("B10").Select
    Selection.Copy
    ActiveWorkbook.Close
    Sheets("ResultsReports").Select
    Range("A2").Select
    ActiveSheet.Paste
End Sub

Much Better! We can work with this. Completing the program is now just a matter of adding some looping logic and "neating things up a bit". The final result is shown in the illustration at the top of the page. Here's the code that created this result:

Sub CopyWebData()
'
' CopyWebData Macro
'
Dim InCellLoc As String
Dim InCellCol As String
Dim InCellRow As String
Dim NameValue As String
Dim AddressValue As String
Dim OutCellLoc As String
Dim OutCellCol As String
Dim OutCellRow As String

InCellCol = "B"
InCellRow = "2"
OutCellRow = "1"
InCellLoc = InCellCol & InCellRow
Sheets("WebAddresses").Select
Do While Range(InCellLoc).Value <> ""
    Sheets("WebAddresses").Select
    Workbooks.Open Range(InCellLoc).Value
    Range("B10:C10").Select
    Selection.UnMerge
    ArticleNameValue = Range("B10").Value
    ActiveWorkbook.Close (False)
    Windows("Start.xls").Activate
    Sheets("ResultsReports").Select
    OutCellRow = CStr(CInt(OutCellRow) + 1)
    OutCellCol = "A"
    OutCellLoc = OutCellCol & OutCellRow
    Range(OutCellLoc).Value = ArticleNameValue
    Sheets("WebAddresses").Select
    InCellRow = CStr(CInt(InCellRow) + 1)
    InCellLoc = InCellCol & InCellRow
Loop
End Sub