Extracting a URL from hyperlinked text in Excel cell

Question

I have a table full of Hyperlinked text in excel, so it's basically a bunch of names but when I click on one, it takes me to some URL in my default browser.

So I am extracting text from this excel table in my program, but the value I get when I extract from these hyperlink cells is that of the string inside, when I want the URL the string is linked to in the excel file.

So I'm thinking there are two ways to do this. Either I can convert all the hyperlinked text in the excel file to the corresponding URLs, or I can use C# to somehow extract the URL value from the cell and not the text.

I don't know how to do either of these things, but any help would be greatly appreciated.

C# code so far:

Excel.ApplicationClass excelApp = new Excel.ApplicationClass();

//excelApp.Visible = true;

Excel.Workbook excelWorkbook = 
excelApp.Workbooks.Open("C:\\Users\\use\\Desktop\\list.xls",
0, false, 5, "", "",false, Excel.XlPlatform.xlWindows, "", 
true, false, 0, true, false, false);

Excel.Sheets excelSheets = excelWorkbook.Worksheets;

string currentSheet = "Sheet1";
Excel.Worksheet xlws = (Excel.Worksheet)excelSheets.get_Item(currentSheet);

string myString = ((Excel.Range)xlws.Cells[2, 1]).Value.ToString();

As for the excel file, it's just one long row of names hyperlinked. For instance cell A2 would contain the text:

Yummy cookie recipe

And I want to extract the string:

http://allrecipes.com//Recipes/desserts/cookies/Main.aspx

Answer 1

You could use a vba macro:

Hit Alt+F11 to open the VBA editor and paste in the following:

Function URL(rg As Range) As String
  Dim Hyper As Hyperlink
  Set Hyper = rg.Hyperlinks.Item(1)
  URL = Hyper.Address
End Function

And then you can use it in your Worksheet, like this:

=URL(B4)

Answer 2

In your code just add

string myString = ((Excel.Range)xlws.Cells[2, 1]).Cells.Hyperlinks[1].Address;

I obviously recommend doing some checks before accessing the "Hyperlinks" property.

Answer 3

VBA function:

1. Hit Alt+F11 (Opens Visual Basic Editor)
2. Click on Insert -> Module (adds a module to your excel file)
3. Paste the code below for the function of GETURL
4. Hit Alt+Q (Closes the Visual Basic Editor)

Now use the =GETURL(cell) to get the URL
Example: =GETURL(A1) will return the URL for the Hyperlink displayed in cell A1

Function GETURL(HyperlinkCell As Range)
    GETURL = HyperlinkCell.Hyperlinks(1).Address
End Function

Source

Answer 4

Use Visual Studio Tools for Office (VSTO) to open Excel workbook and extract all hyperlinks.

---

I put a hyperlink into A1 of Sheet1 in Book1.xlsx: text = "example.com, address = "http://www.example.com"

_Application app = null;
try
{
    app = new Application();

    string path = @"c:\temp\Book1.xlsx";
    var workbook = app.Workbooks.Open(path, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

    var sheets = workbook.Worksheets;
    var sheet = (Worksheet)sheets.get_Item("Sheet1");

    var range = sheet.get_Range("A1", "A1");
    var hyperlinks = range.Cells.Hyperlinks.OfType<Hyperlink>();

    foreach (var h in hyperlinks)
    {
        Console.WriteLine("text: {0}, address: {1}", h.TextToDisplay, h.Address);
    }
}
finally
{
    if (app != null)
        app.Quit();
}

Output:

text: example.com, address: http://www.example.com/

Answer 5

why not use Uri class to convert string into URL:

Uri uri = new Uri("http://myUrl/test.html");

Answer 6

You can use VBA code to achieve this. Press Alt + F11 to open VB editor, Insert a Module and paste the code below:

Sub run()    
    On Error Resume Next    

    For Each hLink In Selection    
        Range(hLink.Address).Offset(0, 1) = hLink.Hyperlinks(1).Address    
    Next    
End Sub

Save your excel file[in excel 2007 and above save as macro enabled...]

Answer 7

Try this:

Excel.Application appExcel = new Excel.Application();
Excel.Workbooks workBooks = appExcel.Workbooks;
Excel.Workbook excelSheet = workBooks.Open("......EditPath", false, ReadOnly: true);

foreach (Excel.Worksheet worksheet in excelSheet.Worksheets)
{
    Excel.Hyperlinks hyperLinks = worksheet.Hyperlinks;
    foreach (Excel.Hyperlink lin in hyperLinks)
    {
        System.Diagnostics.Debug.WriteLine("# LINK: adress:" + lin.Address);
    }
}

Answer 8

I just ran into this issue and this is what worked for me:

I used the FormulaR1C1 extension method for a range. So my code looked like this:

                    for (int r = 2; r <= sheetRange.Rows.Count; r++)
                    {
                        documentRecord = new List<string>();
                        for (int c = 1; c <= wkCol; c++)
                        {
                            documentRecord.Add(sheetRange.Cells[r, c].FormulaR1C1); 
                        }
                        AllRecords.Add(documentRecord);
                    }

When the record is added to the list of records, the value of whatever the cell range was is formatted into a clickable-hyperlink.