Open office extract URL from  hyperlinks - in the adjacent cell

Posted on 2010-08-14
Last Modified: 2013-12-27
I have a column peppered with hyperlinks.

I need to extract the hyperlinks from each cell in my .ods spreadsheet.  

I need them to appear in the cell they exist in , or to the cell to the right.

You can see by checking the Image column, I need the URL's from the hyperlinks.
LVL 20

Accepted Solution

ltlbearand3 earned 500 total points
ID: 33437860
We can take the Macro from your earlier questions ( and modify it to just replace the current cell with the text from the link.  Just update the Macro with the one below.  This will not longer prompt for a sheet to enter, but instead will just update any hyperlink on the current sheet.  I noticed you posted an .xls file but asked in OpenOffice.  This macro is for OpenOffice.  If you need an Excel macro, you will need to let us know that.

REM  *****  BASIC  *****

Option Explicit

Sub ShowAllHyperlinks()

	Dim oDocument as object

	Dim oSheet as Object

	Dim oCellCursor as object	

	Dim intLastRow as integer

	Dim intLastCol as integer

	Dim oNewSheet as object

	Dim intCurRow as integer

	Dim intCurCol as integer

	Dim intNewRow as integer

	Dim oNewCell as object

	Dim oCell as Object

	Dim strLink as String

	' Get access to the document

	oDocument   = ThisComponent 

	' Find Last Cell of Data in Sheet

	oSheet = oDocument.getCurrentSelection.getSpreadSheet

	oCellCursor = oSheet.createCursor()


	intLastRow  = oCellCursor.getRangeAddress().endRow

	intLastCol = oCellCursor.getRangeAddress().endColumn

	intNewRow = 0


	' Loop Through all Cells looking for URLs

	For intCurRow = 0 to intLastRow

		For intCurCol = 0 to intLastCol

			oCell =  oSheet.getCellByPosition(intCurCol, intCurRow)

			' VarType 9 = Object

			If vartype(oCell) = 9 then

				' If Count is greater than 1, we have a cell with a URL

				If oCell.TextFields.Count > 0 Then

					strLink = oCell.GetTextFields.getByIndex(0).URL

					oCell.string = strLink

		   		End If

		   	End If




	msgbox "URL Extraction is Complete"

End Sub

Open in new window


Author Comment

ID: 33439575
-- (in a Mr. Burns voice...)  "Egggcelent!"

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Note: This is the second blog post in a series on email clearinghouses (   Every month t…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now