LibreOffice Macros

Extract URLs from Hyperlinks

REM Source: https://ask.libreoffice.org/en/question/94882/how-to-extract-the-urls-of-text-with-hypertext-into-the-next-column/

Sub Extract_URLs_From_Hyperlinks( iSheetIndex as Integer, iColumnHyperlinks As Integer, iColumnURLs As Integer )
REM <iSheetIndex>:          Sheet index ( 0 = Sheet1 ).
REM <iColumnHyperlinks>:    Column with the Hyperlinks ( 0 = A ).
REM <iColumnURLs>:          Column where the URLs from the Hyperlinks should be extracted to ( 1 = B ).
    On Local Error GoTo Exit_Sub
    Dim oSheet : oSheet = ThisComponent.Sheets.getByIndex( iSheetIndex )
    Dim oCell, oTextFields
    Dim strURL as String
    Dim i As Integer

    REM Traverse Rows:
    For i = 0 to 99999      REM 99999 = set here your maximum number of Rows to process...
        oCell = oSheet.getCellByPosition( iColumnHyperlinks, i )
        oTextFields = oCell.getTextFields()
        If Not isNull( oTextFields ) And oTextFields.getCount() > 0 Then
            strURL = oTextFields.getByIndex( 0 ).URL
            oSheet.getCellByPosition( iColumnURLs, i ).setString( strURL )
        End If
    Next i
Exit_Sub:
End Sub

Sub Main
Extract_URLs_From_Hyperlinks(0, 0, 1)
End Sub