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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

HOW TO: Upload an ISO image to a VMware datastore for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere Host Client, and checking its MD5 checksum signature is correct.  It's a good idea to compare checksums, because many installat…
Is your company's data protection keeping pace with virtualization? Here are 7 dynamic ways to adapt to rapid breakthroughs in technology.
Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 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

21 Experts available now in Live!

Get 1:1 Help Now