Get web page title from URL

  • Thread starter Thread starter copwriter
  • Start date Start date
C

copwriter

I produce a monthly report of the number of page views on a web site.
The data is obtained from Google Analytics, and the pages are
designated by their URLs. A data analysis service we used previously
furnished both the title and URL of each page. The URL isn't terribly
user-friendly--most people want to know what was on the page, not what
its URL was (yes, they can click on the URL, but there are hundreds of
pages in this report). Is there any way to get Excel to extract the
page title of a URL listed in the workbook, and report it in an
adjacent cell?
 
These are hyperlinks in cells?

Maybe you could use a userdefined function to extract the hyperlink from the
cells that have them.

Option Explicit
Function GetURL(Rng As Range) As String
Application.Volatile

Set Rng = Rng(1)

If Rng.Hyperlinks.Count = 0 Then
GetURL = ""
Else
GetURL = Rng.Hyperlinks(1).Address
End If
End Function

So if you had a hyperlink in A1, you could put =getURL(a1) in that adjacent
cell.

Be aware that if you change the hyperlink, then this formula cell won't change
until your workbook calculates.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=getURL(a1)
 
Back
Top