[ACCEPTED]-Extracting a URL from hyperlinked text in Excel cell-vsto
You could use a vba macro:
Hit Alt+F11 to open 3 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 2 then you can use it in your Worksheet, like 1 this:
=URL(B4)
In your code just add
string myString = ((Excel.Range)xlws.Cells[2, 1]).Cells.Hyperlinks[1].Address;
I obviously recommend 1 doing some checks before accessing the "Hyperlinks" property.
VBA function:
- Hit Alt+F11 (Opens Visual Basic Editor)
- Click on Insert -> Module (adds a module to your excel file)
- Paste the code below for the function of GETURL
- 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
0
Use Visual Studio Tools for Office (VSTO) to 3 open Excel workbook and extract all hyperlinks.
I 2 put a hyperlink into A1 of Sheet1 in Book1.xlsx: text 1 = "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/
why not use Uri class to convert string 1 into URL:
Uri uri = new Uri("http://myUrl/test.html");
You can use VBA code to achieve this.
Press 3 Alt + F11 to open VB editor, Insert a Module and paste 2 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 1 2007 and above save as macro enabled...]
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);
}
}
0
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.