Excel - Convert hyperlink to text format

January 2017



Issue


I work with Excel 2003.My problem is, I have to feed a database with old Excel files. There are thousands of hyperlinks. The hyperlink has been transformed to appear as "1". To get my data into the new database I have to import the name in text format. How to find it automatically?

Solution


With VBA:
  • Open the workbook,
  • Make a copy (save as) and work on the copy!
  • In the copy of the workbook:
  • Press ALT + F11
  • Insert/Module
  • Copy/Paste this code:


Sub AfficheNomCompletLienHypertexte() 
Dim Lign As Long, DrLig As Long 
Dim Col As Byte 
Dim NomDuLien As String 

Col = 1 '==> A adapter : n° de la colonne contenant vos liens hypertextes 
With Sheets("Feuil1") '==> A adapter le nom de la feuille contenant vos liens 
    DrLig = Columns(Col).Find("*", , , , xlByColumns, xlPrevious).Row 
    For Lign = 1 To DrLig 
        If .Cells(Lign, Col).Hyperlinks.Count = 1 Then 
            NomDuLien = .Cells(Lign, Col).Hyperlinks(1).Address 
            .Cells(Lign, Col).Hyperlinks.Delete 
            .Cells(Lign, Col).Clear 
            ActiveSheet.Hyperlinks.Add Anchor:=.Cells(Lign, Col), Address:=NomDuLien, TextToDisplay:=NomDuLien 
        End If 
    Next Lign 
End With 
End Sub


You can adapt this code to your spreadsheet.
Press ALT+F8, select "AfficheNomCompletLienHypertexte" and run.

Thanks to Pikaju for this tip.

Related


Published by aakai1056. Latest update on June 26, 2012 at 09:54 AM by aakai1056.
This document, titled "Excel - Convert hyperlink to text format," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).