Extracting hyperlink destinations from Excel (Document Library item URLs)

I recently needed the destination URL for several hundred items across layers of nested folders in a giant SharePoint Document Library.    Right clicking with “copy link location” just wasn’t going to work here.   The datasheet view, Excel, and Access all came to mind as possible tools.   In then end a quick Excel UDF (User Defined Function) helped get the job done.   This is easily done against SharePoint Document Libraries because the default Title column links to the file item URL.

  1. Choose “Export to Spreadsheet” from your document library
  2. Open the .IQY file when prompted
  3. Press Alt+F11, then Insert > Module
  4. Paste in this code:
  5. Function GetAddress(HyperlinkCell As Range)
        GetAddress = Replace _
        (HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
    End Function
  6. Save the workbook.   Note:  You might be prompted to save as XLSM for a macro enabled workbook, change the file type, and save.
  7. Use the =getaddress(cell) function to see the URLs  (screenshots below)

 

 

clip_image002

clip_image004

clip_image006

clip_image008

clip_image010

clip_image012

clip_image014

clip_image016

clip_image018

clip_image020

 

Sources Cited

http://www.ozgrid.com/VBA/HyperlinkAddress.htm

Function GetAddress(HyperlinkCell As Range)

    GetAddress = Replace _

    (HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")

End Function

Return to Top ▲Return to Top ▲