Ten useful LibreOffice Macro Recipes

Recipe 0: How to create a LibreOffice macro

Recipe 1: Read cell contents

Sub read_cell
dim document as object
document = ThisComponent
sheet = document.Sheets(0)
MsgBox(sheet.getCellByPosition(0, 0).String)
End Sub

Recipe 2: Change cell contents

Sub change_cell
dim document as object
document = ThisComponent
sheet = document.Sheets(0)
sheet.getCellByPosition(0, 0).String = "Hola Mundo!"
MsgBox("Done")
End Sub

Recipe 3: Search and Replace

Sub replace_text
Dim names() As String
Dim surnames() As String
Dim n As Long
Dim document As Object
Dim sheets as Object
Dim sheet as Object
Dim replace As Object

names() = Array("Linus", "Richard", "Peter", "Greg", "Bill")
surnames() = Array("Torvalds", "Stallman", "Anvin", "Kroah", "Gates")
document = ThisComponent rem .CurrentController.Frame
rem sheet = doc.CurrentSelection.Spreadsheet
sheets = document.getSheets()
sheet = sheets.getByIndex(0)
replace = sheet.createReplaceDescriptor rem document.createReplaceDescriptor in case of Writer
rem replace.SearchRegularExpression = True
For n = lbound(names()) To ubound(names())
replace.SearchString = names(n)
replace.ReplaceString = surnames(n)
sheet.replaceAll(replace)
Next n
MsgBox("Done")
End Sub

Recipe 4: Regular Expressions

Sub replace_with_regex
Dim names() As String
Dim surnames() As String
Dim n As Long
Dim document As Object
Dim sheets as Object
Dim sheet as Object
Dim replace As Object

pattern = "\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}\b" rem regex pattern to match any email address
document = ThisComponent rem .CurrentController.Frame
sheets = document.getSheets()
sheet = sheets.getByIndex(0)
replace = sheet.createReplaceDescriptor rem document.createReplaceDescriptor in case of Writer
replace.SearchRegularExpression = True
replace.SearchString = pattern
replace.ReplaceString = "foo@bar.com"
sheet.replaceAll(replace)

MsgBox("Done")
End Sub

Recipe 5: Show File-open dialog

function show_open_dialog
dim aurl as object
dim s as string
dim oDlg as object

oDlg = createUnoService("com.sun.star.ui.dialogs.FilePicker")
oDlg.setMultiSelectionMode(false)
oDlg.appendFilter("CSV Files (.csv)", "*.csv" )
oDlg.execute
aUrl = oDlg.getFiles()

s = aUrl(0)
if len(s) > 0 then
MsgBox("File Selected: " & s & chr(13))
end if
show_open_dialog = s
end function

Recipe 6: Show File-save dialog

function show_save_dialog
dim aurl as object
dim s as string
dim oDlg as object

sFilePickerArgs = Array(_
com.sun.star.ui.dialogs.TemplateDescription.FILESAVE_AUTOEXTENSION )
oDlg = createUnoService("com.sun.star.ui.dialogs.FilePicker")
oDlg.initialize(sFilePickerArgs())
oDlg.setMultiSelectionMode(false)
oDlg.appendFilter("CSV Files (.csv)", "*.csv" )
oDlg.setTitle("Save As....")

if oDlg.execute() then
aUrl = oDlg.getFiles()
s = aUrl(0)
if len(s) > 0 then
MsgBox("File Selected: " & s & chr(13))
end if
else
s = ""
end if
show_save_dialog = s
end function

Recipe 7: File I/O: Read from files

sub file_io_read
dim v1, v2, v3
filename = "/home/prahlad/data/test.csv"
num = FreeFile()
open filename for input as #num
do while not eof(num)
input #num, v1, v2, v3
print v1 & "::" & v2 & "::" & v3
loop
close #num
msgbox "Done"
end sub

Recipe 8: File I/O: Write to files

sub file_io_write
filename = "/home/prahlad/data/dummy.csv"
num = FreeFile()
open filename for output as #num
write #num, "col1", "col2", "col3"
write #num, "1", "2", "3"
write #num, "4", "5", "6"
close #num
msgbox "Done"
end sub

Recipe 9: Load data from a CSV file

sub load_from_csv
fname = show_open_dialog
if len(fname)>0 then
dim fileProps(1) as new com.sun.star.beans.PropertyValue
fileProps(0).Name = "FilterName"
fileProps(0).Value = "Text - txt - csv (StarCalc)"
fileProps(1).Name = "FilterOptions"
fileProps(1).Value = "44,34,76,1,,0,false,true,true,false"
document = StarDesktop.loadComponentFromURL(fname, "_blank", 0, fileProps())
end if
msgbox "Done"
end sub

Recipe 10: Copy text to clipboard

sub copy_to_clipboard
oClip = CreateUnoService("com.sun.star.datatransfer.clipboard.SystemClipboard")
oTR = createUnoListener("TR_", "com.sun.star.datatransfer.XTransferable")
oClip.setContents(oTR, null)
msgbox "Done"
end sub

Function TR_getTransferData( aFlavor As com.sun.star.datatransfer.DataFlavor ) As Any
If (aFlavor.MimeType = "text/plain;charset=utf-16") Then
TR_getTransferData = "Hola!"
EndIf
End Function

Function TR_getTransferDataFlavors() As Any
Dim aF As new com.sun.star.datatransfer.DataFlavor
aF.MimeType = "text/plain;charset=utf-16"
aF.HumanPresentableName = "Unicode-Text"
TR_getTransferDataFlavors = Array(aF)
End Function

Function TR_isDataFlavorSupported( aFlavor As com.sun.star.datatransfer.DataFlavor ) As Boolean
TR_isDataFlavorSupported = (aFlavor.MimeType = "text/plain;charset=utf-16")
End Function

Demo

References

--

--

--

Freelance Programmer and Writer. Educate and solve problems is my mantra. I work on full stack web development and automation projects.

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Prahlad Yeri

Prahlad Yeri

Freelance Programmer and Writer. Educate and solve problems is my mantra. I work on full stack web development and automation projects.

More from Medium

MLX90614 Distance to Spot Ratio

Nouveau Begins Shifting Around Code For Use By New Driver — Vulkan And/Or New Driver

UART | Microcontroller Communication With The Terminal

The 12-Factors moderating container services…(3/4)