[RESOLVED] Export from Listbox to Excel

[BULK]

I have an app which exports data to Excel. In excel the data is added, but with an square. This makes the calculations in Excel impossible.
I've tried to use the replace in text changed, but with no luck.

The code that export Listbox lines to Excel:

  Dim trace As Integer
        For trace = 1 To ListBox1.Items.Count
            xlWorkSheet.Cells(trace + 18, 2).Value = ListBox1.Items(trace - 1)
        Next

ListBox1_TextChanged:

  ListBox1.Text = Replace(ListBox1.Text, Chr(8), String.Empty) 'backspace  
        ListBox1.Text = Replace(ListBox1.Text, Chr(9), String.Empty) ' tab
        ListBox1.Text = Replace(ListBox1.Text, Chr(10), String.Empty) 'LF
        ListBox1.Text = Replace(ListBox1.Text, Chr(11), String.Empty) 'vertical tab
        ListBox1.Text = Replace(ListBox1.Text, Chr(12), String.Empty) 'form feed, new page 
        ListBox1.Text = Replace(ListBox1.Text, Chr(13), String.Empty) 'CR
        ListBox1.Text = Replace(ListBox1.Text, Chr(32), String.Empty) 'Space
        ListBox1.Text = Replace(ListBox1.Text, Chr(35), String.Empty)
        ListBox1.Text = Replace(ListBox1.Text, Chr(127), String.Empty)
        ListBox1.Text = Replace(ListBox1.Text, Chr(129), String.Empty)
        ListBox1.Text = Replace(ListBox1.Text, Chr(141), String.Empty)
        ListBox1.Text = Replace(ListBox1.Text, Chr(143), String.Empty)
        ListBox1.Text = Replace(ListBox1.Text, Chr(144), String.Empty)
        ListBox1.Text = Replace(ListBox1.Text, Chr(157), String.Empty)
        ListBox1.Text = Replace(ListBox1.Text, Chr(249), String.Empty)
        ListBox1.Text = Replace(ListBox1.Text, Chr(254), String.Empty) 'Black box
        ListBox1.Text = Replace(ListBox1.Text, Chr(255), String.Empty) 'White Space

[Pradeep1210]

What happens if you do:

  Dim trace As Integer
        For trace = 1 To ListBox1.Items.Count
            xlWorkSheet.Cells(trace + 18, 2).FormulaR1C1 = ListBox1.Items(trace - 1).ToString
        Next

Also you don't need that code in ListBox1_TextChanged event.

Can you attach a sample file?

[BULK]

Hi, thanks. The result was the same with your code. The text is pasted into a richtextbox, by the user. So I have no example, but it's just numbers.

I also tried to export from a richtextbox, also with the same result.

Export from textbox code:

  Dim Text As String = String.Empty
        Dim i As Integer = 0
        Dim Lines As Integer = txtTraceSpeed.Lines.Length

        For i = 0 To Lines - 1
            Text = txtTraceSpeed.Lines(i).ToString()
            xlWorkSheet.Range("B" & i + 1).Value = Text
        Next

The square seems to be a tab, but i'm not sure.

[BULK]

It could be resolved with this:

lstTraceSpeed.Text = Replace(lstTraceSpeed.Text, Chr(9), String.Empty)

But only for the textbox, this didn't work for the listbox.

[Siddharth Rout]

xlWorkSheet.Cells(trace + 18, 2).Value = ListBox1.Items(trace - 1)

Try triming the value before exporting it to excel...

Edit: I had not refresh the page!!!! Damn