Making Rows for Horizontal Pasting Inside a Single Text Box?

CompSciSarah · December 16, 2021, 6:59pm

(This is a question for LO: Calc)

As a part of my workflow, I need to copy-paste data from Libreoffice to a shared Excel doc that always has a specific format.

I noticed on the system clipboard that when I copy rows horizontally, it copies in the format “A1 (long space character) A2”. I tried to replicate this format, but am missing some special character (like a CHAR(10), but obviously not 10).

I have a vertical column of rows (Like: B1 C1 D1) that I can’t change the formatting of for various reasons. Can I make a single box that makes the copy paste format for a horizontal row like:
=A1 & CHAR(MAGIC) & B1 & CHAR(MAGIC) & C1
to give a paste of A1, B1, C1 horizontally (essentially transposing them, but in a copy-paste format)?

joshua4 · December 18, 2021, 5:31pm

It’s hard to say if you are “seeing” the clipboard format or just seeing however your program is displaying the clipboard format. As you may know, the clipboard contains metadata about the copied information, sort of like a MIME type in email attachments.

Can you post an example LO document? Are you really wanting to transpose, or are you just saying that since rows break across rows as you expect when you paste, you would like columns to break across columns, too? Can you get the effect you want just copying from LO and then pasting back into LO?

CompSciSarah · December 18, 2021, 7:04pm

Basically just a transpose operation.
Take vertical cells, which are all combined in a single cell.

When that one cell is copied and pasted, it pasts the vertical rows as columns (transpose operation)

joshua4 · December 19, 2021, 3:45am

Do you mean like this, going from green to red? I’m sorry, but your use of row/column/vertical doesn’t add up.

A column (naturally both vertical and of rows) would be like B1,B2,B3…

CompSciSarah · December 19, 2021, 3:58pm

Yes, so if you copied green, it would paste as the orange-redish color.

Villeroy · December 19, 2021, 4:20pm

Data>Text to columns … [OK] In this special situation, it splits the text rows in separate rows rather than columns.
Copy, Paste-Special with transpose option.

joshua4 · December 19, 2021, 8:06pm

This is better than hand entry for any larger dataset, but I’d say this is a bit of a workaround at least up to Excel 2016, since up to then Excel recognizes the picture and text put on the clipboard by LO, but doesn’t offer a way to Transpose text/cell data during paste. That means a person would have to paste into Excel as is then copy and paste again, using Excel’s Paste special…Transpose [or some in-workbook transpose operation].

Sorry to turn this into an Excel forum, but to get exactly what the OP wants, things really should happen in Excel. Here’s a macro that can be assigned to a key combination in Excel. It will paste either multi-row, single-cell text or multi-row, multi-cell text selected and copied in LO Calc into a multi-column formation in Excel. The pasting will happen starting at the currently selected cell in Excel. This will do exactly as the OQ asked, no need to use the Data functionality in LO first.

Option Explicit

Public Clipboard As New MSForms.DataObject

Sub TransposePasteFromLO()
    'Clipboard access from Excel Help HQ
    'Tested on Excel 2016
    'Add a user form to this project (VBAProject/Workbook)
        'so that VBA autoloads MSForms...it may not be available
        'as a reference item under Tools-References.
        'To do this right-click on Modules, select Insert-UserForm
    Dim DataObj As New MSForms.DataObject
    Dim Selection As Range
    Dim Contents As String
    Dim Result As Variant
    Dim ResultLength As Integer
    Dim Value As Variant
    Dim Index As Integer
    
    Set Selection = Application.Selection
    DataObj.GetFromClipboard
    Contents = DataObj.GetText
    
    'For multi-row text within a cell
    Result = Split(Contents, Chr(10))
    
    'For multi-row selection
    If Not IsArray(Result) Then
        Result = Split(Contents, vbCrLf)
    End If
    
    'For a combination of multi-row text and multi-row selection
        'result will be multi-row selection as separate cells
        'and multi-row text within a single row of a single cell separated by a space
    
    ResultLength = UBound(Result) - LBound(Result) + 1
    Index = 0
    For Each Value In Result
        Selection.Offset(0, Index).Value = Value
        Index = Index + 1
        If Index = ResultLength - 1 Then Exit For 'Don't write blank cell at end
    Next
End Sub 'TransposePasteFromLO

If OP needs assistance on adding this macro or the dummy UserForm, I’d be willing to respond with the caveat that this is not an Excel forum.