is it possible to get a hyperlinked info from a website directly to excel?

Sometimes I need to get underlying e-mail addresses for several (20-30) hyperlinked texts (on a website) and 开发者_运维百科copy them into excel. Is it possible to do it directly from a website rather than copying the info first as HTML into excel and then running a macro to get it from there. That's what I currently use:

Function emailAddress(cell)
    On Error Resume Next
    emailAddress = Replace(cell.Hyperlinks(1).Address, "mailto:", "")
    If emailAddress = 0 Then 
       emailAddress = ""
End Function

but I wonder if there is a way to bypass this step

There are a few ways you can accomplish this task.

The first is to load the website data right into a workbook:

 Workbooks.Open Filename:="http://yoururlhere.com/data.html"

This is the most straightforward, as I'm sure you can specify if you wanted your data to go to a particular sheet.

The other would be to hook Internet Explorer using Excel. See this for example. This involves knowing more about how to access the different DOM elements of the page to get the information you need.

You could probably also write your data access in C# or VB.NET and use their Office Automation libraries to control Excel, but that will be more convoluted.

You could use View - Source in your webbrowser and copy the raw HTML into Excel. Then use Text to Columns to split on ":" plus some other clean up stuff. I'm not sure that saves you much.