Set Clipboard text from listbox

Hi,

I have success to copy selected rows into clipboard using this code below,

[code] Customerslist11.SelectionType = 1
dim a,b as Integer
a=val(PopupMenu3.Text)-1
b=val(PopupMenu4.Text)-1
For i as Integer = a to b
if Customerslist11.Selected(i) then
s = s+ Customerslist11.cell(i,0) + EndOfLine
end if
Customerslist11.Selected( i ) = True
Customerslist11.SetFocus
Next

Dim c as new Clipboard
c.SetText(s)
c.Close
[/code]

This code is working only to copy 1 column. How to modified it, if I want to copy for two columns

modified this code into

s = Customerslist11.cell(i,0) +Customerslist11.cell(i,1) + EndOfLine

but no work.

I want to paste it in Excel Spreadsheet.

any helps ?

Thanks
Regards,
Arief

s = Customerslist11.cell(i,0) + Chr(9) + Customerslist11.cell(i,1) + EndOfLine

Added Chr(9): a Tab.

If your Row have only two Cells, use instead:

s = Customerslist11.Cell(i,-1) + EndOfLine

Nota: does not works is meaningles (says nothing). If you get all text in a single Excel Cell, it is because you coded it that way.

Add a break point on the c.Close line / run and check what you have in s. Eventually, set a breakpoint earlier / run and watch what happens with your code.

Hi,

s = Customerslist11.cell(i,0) + Chr(9) + Customerslist11.cell(i,1) + EndOfLine

is working, I meant copying from two columns. its resulting text in two columns .
but the loop is not working, I was selected for 10 rows, its only copy last row only.

thanks
regards,
arief

Did you checked in the debugger a and b values ?

I had this trouble earlier today: the loop indice was not what I was thinking… so I set a value to be sure, checked in the debugger and understand where I was wrong.

Try to set a to 0 and b to Customerslist11.ListCount // I hope your listbox is not thousands of Rows

In you code, why:

Customerslist11.Selected( i ) = True Customerslist11.SetFocus

IMHO: these are useless, comment them.

[quote=409403:@Arief Sarjono]Hi,

s = Customerslist11.cell(i,0) + Chr(9) + Customerslist11.cell(i,1) + EndOfLine

is working, I meant copying from two columns. its resulting text in two columns .
but the loop is not working, I was selected for 10 rows, its only copy last row only.

thanks
regards,
arief[/quote]
Maybe you missed an s?

s = s + Customerslist11.cell(i,0) + Chr(9) + Customerslist11.cell(i,1) + EndOfLine

You can use Listbox.Cell( row, -1 ) to get all columns at once. The column content will be separated with tabs and unless your list cells contain tabs then this will work well.

Also it’s a good idea to avoid ‘s = s + …’ in a loop as it gets exponentially slower. Creating an array of strings and using Join can be much more efficient. Sample code:

dim lines() as String 'lines.Append Listbox1.Heading( -1 ) // Optional - uncomment to also copy the column headers for row as Integer = 0 to Listbox1.ListCount-1 if Listbox1.Selected( row ) then lines.Append Listbox1.Cell( row, -1 ) // The -1 means all columns with a tab delimiter end next dim result as String = Join( lines, EndOfLine )

Timing:

[code]Time in seconds using the two different techniques.
Three list columns and each cell was: str(row) + “AAAAAAA” (~40 characters per row).

Selected Using Using
Rows s=s+ Join


1000     0.006    0.003

10000 0.280 0.027
100000 38.300 0.250
200000 642.700 0.505[/code]

Dear Mr. Schwarz,

a & b are defined for marking start and end row number, even I do changing it into integer value, it still works with no error.

[quote]
Customerslist11.Selected( i ) = True
Customerslist11.SetFocus[/quote]
this code I use to marked selected rows in the listbox without clicking the cell in listbox, so i can see how many the rows is selected.

The first code is working as expected, but when it change to copy value from two columns its paste only the last selected rows, adding an ‘s’ as Mr. Poo suggestion, its paste nothing.

My goals is copying from listbox, then paste it into Ms-Excel.

Thanks
Reagrds,
Arief

Well, you have to move or change your code a little:

if Customerslist11.Selected(i) then s = s + Customerslist11.cell(i,0) + Chr(9) + Customerslist11.cell(i,1) + EndOfLine end if Customerslist11.Selected( i ) = True Customerslist11.SetFocus
Your ‘If’ is testing if the row is selected, but you set the selection to True after the If, that’s why ‘s’ at the end is empty, so you can change it to:

Customerslist11.Selected( i ) = True Customerslist11.SetFocus if Customerslist11.Selected(i) then s = s + Customerslist11.cell(i,0) + Chr(9) + Customerslist11.cell(i,1) + EndOfLine end if
or simple remove the if and keep the s, because you already set what rows to copy with PopUpMenu3 and PopUpMenu4 and ‘a’ / ‘b’

Test this:

[code] Customerslist11.SelectionType = 1
dim a,b as Integer
a=val(PopupMenu3.Text)-1
b=val(PopupMenu4.Text)-1
For i as Integer = a to b
s = s + Customerslist11.cell(i,0) + Chr(9) + Customerslist11.cell(i,1) + EndOfLine
Customerslist11.Selected( i ) = True
Customerslist11.SetFocus
Next

Dim c as new Clipboard
c.SetText(s)
c.Close[/code]

yes, this is the problem.

thanks, its solved now.

regards,
arief