I have the need to copy all contents from a Listbox and enable that information to be pasted into a Spreadsheet. My goal was to enable bot OS X and Windows capability for use in Numbers and Excel
The following is what I came up with. After testing this works in both Excel 2016 in Windows 10 and Numbers 4.0.5 OS X 10.12.2. The purpose of this is to share and to see if there are other maybe better ways to accomplish this task.
In ConstructContextualMenu of the listbox add [quote] base.Append( New MenuItem( “Select All and Copy” ) )[/quote]
In ContectualMenuAction add [quote] Dim y As Integer
Dim my_str As String
Select Case hitItem.Text
Case "Select All and Copy"
'-- Get headers
For y = 0 to Me.ColumnCount - 1
If y = 0 Then
my_str = Me.Heading(y)
Else
my_str = my_str + Chr(9) + Me.Heading(y) '-- For most spreadsheets Chr(9) which is VT (Vertical Tab) will enable column seperation when pasting
End
Next
my_str = my_str + EndOfLine
For row As Integer = 0 To Me.ListCount - 1 // Rows are zero-based
Me.Selected(row)=True
For y = 0 to Me.ColumnCount - 1
If y = 0 Then
my_str = my_str + Me.Cell(row,y)
Else
my_str = my_str + Chr(9) + Me.Cell(row,y)
End
Next
my_str = my_str + EndOfLine
Next
End
'--- Now that we have all data from Listbox prepared for a spreadsheet place it in clipboard
Dim c as clipboard
c=new clipboard 'C should never be nil, anyway...
c.text= my_str 'The selection
c.close
return true[/quote]
Now you should be able to paste into Numbers or Excel. Headers should be placed in top row and data will follow
Comments, improvements or other ways to accomplish are welcomed
just get all the text from all cells in a big string.
Using chr(9) to separate columns and endofline for rows.
Than put the text on the clipboard.
Can you do that yourself?
@Christian Schmitz - that’s what the solution I shared does andincludes an explanation of VT. I am curious about other solutions to accomplish the same and be cross platform capable
Sorry I forgot to open that source code section.
Using a string array and join function is more efficient than all the + operations.
To get the Header:
my_str = Me.Heading(-1) + EndOfLine
To get the core data from the Listbox:
my_str = Me.Row(-1,-1) + EndOfLine
To get both together:
my_str = Me.Heading(-1) + EndOfLine
my_str = my_str + Me.Row(-1,-1) + EndOfLine
Use your code to place the data in the Clipboard.
Ask if there is an error (I have troubles to run Xojo / macOS Sierra ß.
Where do you place the code ?
An error occured while I clicked in Post a Reply
sorry.
And simply call the menu “copy all”
[code]Select Case hitItem.Text
Case “Copy All”
dim AllText as String
AllText = me.Heading(-1) + EndOfLine
AllText = AllText + me.cell(-1,-1)
dim c as new Clipboard
c.text = AllText
c.close
End Select[/code]
I am not sure that will works in a menu. I forgot why, but I suspect that I had to subclass the Listbox to be able to do that.
That is why I asked where the code was put.
Carl ?
I was not able to get copy all to work. If I manually selected the menu item than all was good. Otherwise In code all I could get was the first list box cell
@Christian Thanks. I do forget about reduced efficiency with + operation. That will be beneficial with large listbox contents
??? Whom do you refer to?
My code works perfectly fine. Did you adjust your menu text accordingly?
And what do you mean by “in code”?
P.S. Added c.close to the code. Maybe that makes a difference on Windows (I’m on a Mac). Shouldn’t as the clipboard is destroyed and its data made available at the end anyway but ir can’t hurt.
Carl:
Put the code in a Pushbutton (and change Me. by the name of the Listbox).
If this works, that means you have yo subclass the Listbox and put the Copy All Menu Handler there.
I will try it in some minutes after a reboot on El Capitan.
Im back.
In a new project, put a small Listbox, a TextArea and a button,
keep their names as is.
ListBox1:
Sub Open()
Dim LoopIdx As Integer
Dim RowCont As String
RowCont = “Zero” + Chr(9) + “One” + Chr(9) + “Two” + Chr(9) + “Three”
Me.ColumnCount = 4
Me.HasHeading = True
Me.Heading(-1) = RowCont
For LoopIdx = 0 to 9
Me.AddRow Split(RowCont,Chr(9)) // “Zero”,“One”,“Two”,“Three”
Next
End Sub
PushButton1:
Sub Action()
Dim AllText As String
AllText = Listbox1.Heading(-1) + EndOfLine
AllText = AllText + Listbox1.cell(-1,-1)
dim c as new Clipboard
c.text = AllText
c.Close
End Sub
Run,
Ctrl+V and watch what you get in the TextArea1 Control (normally what you put in the Clipboard.
Next: the Copy All MenuItem.
@Emile: you do not have to subclass.
OK: I added a Copy All (EditCopyAll) MenuItem,
Add a Menu Handler (EditCopyAll)
Paste the code from the PushButton.
And, Yes (Marcus, you beat me on this one too), no need to subclass. That certainly was for Cut.
PS: my example to fill the Listbox is bad: Id better add the loop index into one cell.
What I would do is to make it an Extension. Add a Module, call it ListBoxExtensions, and add the following method
[code]Method CopyAllToClipboard(extends lb as ListBox)
dim AllText as String
AllText = lb.Heading(-1) + EndOfLine
AllText = AllText + lb.cell(-1,-1)
dim c as new Clipboard
c.text = AllText
c.close[/code]
Now you can simply call the CopyAllToClipboard for any ListBox like this
ListBox1.CopyAllToClipboard
(and it also nicely autocompletes)
Nice.
If EditCopy is used, a subclass is needed.
WARNING: the example shared far above (by me) is a real bad example and, please, do not used it in real project.
I do not select the Listbox (the TextArea have the cocus): very nasty user interface coding. Only shared to make a point.
Carl, is it OK for you now ?
Hey !
I just realized
Select All
Copy
is the same as Copy All !
@Emile S: I could not get that to work as a contextual action. Manually with mouse click on Menu Item yes - program in code no. Must be operator error
@Markus W: Agree with Emile - very Nice. Works in both OS X and Windows 10. I have never used ‘Extends’ in any manner and was not aware. Will need to lookup that feature to see where else that can be utilized
This is what I was hoping for - a learning experience
I would like to thank everyone for their input. Cheers