openOfficeScriptForLinksExtraction

openOfficeScriptForLinksExtraction

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()
   	oCellCursor.gotoEndOfUsedArea(False)
	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
 		Next
 	Next
 	
	msgbox "URL Extraction is Complete"

End Sub

alt + F11 in libreoffice spreadsheet, then paste the script and press run in top bar.
Replaces all hypertext links in table for a cell name.