A LibreOffice Calc macro to copy cell ranges as clean, pre-formatted plain text by bypassing the bloated system clipboard via a hidden Writer proxy.

libreOfficeCopyMacro.md.md

LibreOffice Calc "copy as text" macro

The code

Sub Copy_With_Custom_Separator
    ' --- CONFIGURATION (Set your characters here) ---
    Dim sepCol As String : sepCol = " | "    ' Column separator (tab is Chr(9))
    Dim sepRow As String : sepRow = Chr(10)  ' Row separator (Chr(10) is \n )
    ' ------------------------------------------------

    Dim sel As Object
    Dim r As Long, c As Long
    Dim txt As String
    Dim oDoc As Object
    Dim oDispatcher As Object
    
    oDoc = ThisComponent
    sel = oDoc.CurrentSelection
    
    If IsNull(sel) Then Exit Sub
    
    txt = ""
    
    ' Handle single cell selection
    If sel.supportsService("com.sun.star.table.Cell") Then
        txt = sel.String
    ' Handle range selection
    ElseIf sel.supportsService("com.sun.star.table.CellRange") Then
        For r = 0 To sel.Rows.Count - 1
            For c = 0 To sel.Columns.Count - 1
                txt = txt & sel.getCellByPosition(c, r).String
                
                ' Column separator (if not the last column)
                If c < sel.Columns.Count - 1 Then txt = txt & sepCol
            Next c
            
            ' Row separator (if not the last row)
            If r < sel.Rows.Count - 1 Then txt = txt & sepRow
        Next r
    Else
        Exit Sub
    End If
    
    If txt = "" Then Exit Sub

    ' --- WRITER PORTAL (Filter for clean text) ---
    Dim oDummyDoc As Object
    Dim oCursor As Object
    Dim pProp(0) As New com.sun.star.beans.PropertyValue
    pProp(0).Name = "Hidden"
    pProp(0).Value = True
    
    ' Create a hidden Writer document to avoid copying Calc's graphical layers
    oDummyDoc = StarDesktop.loadComponentFromURL("private:factory/swriter", "_blank", 0, pProp())
    oDummyDoc.Text.String = txt
    
    ' Select text in the hidden document
    oCursor = oDummyDoc.Text.createTextCursor()
    oCursor.gotoEnd(True)
    oDummyDoc.CurrentController.select(oCursor)
    
    ' Dispatch copy command from the clean Writer environment
    oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    oDispatcher.executeDispatch(oDummyDoc.CurrentController.Frame, ".uno:Copy", "", 0, Array())
    
    ' Close the temporary helper document
    oDummyDoc.close(True)

    ' Visual feedback in the status bar
    oDoc.CurrentController.StatusIndicator.start("Text copied successfully!", 100)
    Wait 500
    oDoc.CurrentController.StatusIndicator.end()
End Sub

The Problem: The "Invisible" Clipboard Mess

When you hit Ctrl+C in LibreOffice Calc, the system creates a massive "Clipboard Object" to satisfy every possible recipient. Instead of simple text, the clipboard is flooded with:

Star Embed Source (XML), Star Object Descriptor (XML), GDIMetaFile, EnhancedMetafile, MetaFilePict, image/svg+xml, PNG, DeviceIndependentBitmap, Windows Bitmap, HTML (HyperText Markup Language), HTML Format, SymbolicLink, Link, DataInterchangeFormat, System.String, UnicodeText, Text, Rich Text Format, and Richtext Format.

The receiving app then chooses the format. Often, it grabs bloated HTML or a low-quality graphical representation instead of plain text.

For instance, copying cells as an image produces a technical mess: the anti-aliasing is so poor that it generates 154 different colors for a simple black-and-white grid. Out of 4,343 non-white pixels, only 13% are actual black (#000), resulting in blurry, washed-out text. While Google is full of tips on how to paste without formatting, it’s almost impossible to find information on how to copy without formatting at the source.

What didn't work and why

• Simple uno:copy command: Triggers the same bloated system behavior and provides no control over the output.
• Legacy SystemClipboard scripts: Many API-based solutions are so outdated they cause modern LibreOffice to crash immediately.
• Temporary sheet workaround: While it avoids some metadata, you lose all control over delimiters and structure, often breaking the data.

The Solution: A "Writer Portal"

The main advantage of using a manual loop over copying a range is total control over delimiters. In lines 3 and 4, the configuration section allows you to define custom column (sepCol) and row (sepRow) separators using strings (e.g., " | ") or ASCII codes like Chr(9) (Tab) or Chr(10) (New Line).

The macro functions by creating a "portal" through a hidden Writer document. It launches a background swriter instance, injects the constructed string, and triggers the copy command from there. Because the Writer environment is free from Calc’s graphical layers and cell metadata, only clean, filtered text is sent to the system clipboard. The temporary helper document is closed immediately after, leaving no trace.

Support open scripts and encourage free help!

If this script saved your sanity (and your clipboard), feel free to support my work!

Click the button below to buy me a coffee:

Or use the direct link: https://www.buymeacoffee.com/krzysiunet