vbaexcelopenoffice.org

How to port Excel VBA macro to OpenOffice macro?


How to port Excel VBA macro to OpenOffice macro?

Here is macro for creating hyperlinks:

Sub HyperMaker()
    Dim r As Range
    Dq = Chr(34)
    For Each r In Selection
        r.Formula = "=HYPERLINK(" & Dq & "http://" & r.Text & Dq & ";" & Dq & r.Text & Dq & ")"
    Next r
End Sub

I tried convert this macro to OpenOffice macro (using http://www.business-spreadsheets.com/vba2oo.asp)

Sub HyperMaker()
Dim r As Dim oSheet as Object[n]oSheet = ThisComponent.CurrentController.ActiveSheet[n]oSheet.getCellRangeByName($1)
Dq = Chr(34)
For Each r In Selection
r.Formula = "=HYPERLINK(" & Dq & "http://" & r.Text & Dq & ";" & Dq & r.Text & Dq & ")"
Next r
End Sub

But got errors: BASIC syntax error: Unexpected symbol: Dim. , Expected:,.

Replacing Dim with comma not help. How to make it work in OpenOfffice?


Solution

  • Solution:

    Sub makeHyperlinks()
        Dim sh As Object, z As Object, c As Object
        Dim qCells As Object, enuCells As Object
        sh = ThisComponent.Sheets.getByName("YourSheetName")
        z = sh.getCellRangeByName("B1:B5") ' your column (or rectangle)
        qCells = z.queryContentCells(-1)
        enuCells = qCells.Cells.createEnumeration
        Do While enuCells.hasMoreElements
          c = enuCells.nextElement
          c.Formula = "=HYPERLINK(""http://" & c.String & """)"
        Loop
    End Sub