Export Data from listbox to Excel

Question

sonia sardana -13 Posting Whiz

18 Years Ago

hey I faced the interview Question -

Name Textbox
EMail Textbox
Mobile Textbox
Button-ADD

First he told me to add the three textboxes values into the listbox.
Its simple--I have done it.

Listbox1.items.add(me.textbox1.text & " " & me.textbox2.text & " " & me.textbox3.text)

Then he told me that draw one more button -PRINT & on PRINT Button click the listboxes
information are exported to EXCEL to the individual columns.
Means name in first column & email in second column & Mobile in Third Column.
Plz send me the coding of this bz I have no idea from where to start it.

.net:vb.net

5 Contributors
16 Replies
1K Views
4 Years Discussion Span
Latest Post 13 Years Ago Latest Post by Reverend Jim

Dani AI

Generated 10 Months Ago

Nice work getting the add-to-list step done, . Building on ’s approach, you can export the whole list in one shot, write a header row, and avoid fragile string parsing. When adding to the ListBox, join the three fields with a safe delimiter (for example: Name|Email|Mobile). Then bulk-write a 2‑D array to Excel (much faster than setting cells one by one).

' Imports Excel = Microsoft.Office.Interop.Excel
' Imports System.Runtime.InteropServices

Private Sub btnPrint_Click(sender As Object, e As EventArgs) Handles btnPrint.Click
    If ListBox1.Items.Count = 0 Then Return

    ' Build a 2-D array: headers + rows
    Dim rows = ListBox1.Items.Count
    Dim data(0 To rows, 0 To 2) As Object
    data(0, 0) = "Name" : data(0, 1) = "Email" : data(0, 2) = "Mobile"

    For i As Integer = 0 To rows - 1
        Dim parts = ListBox1.Items(i).ToString().Split("|"c)
        data(i + 1, 0) = If(parts.Length > 0, parts(0).Trim(), "")
        data(i + 1, 1) = If(parts.Length > 1, parts(1).Trim(), "")
        data(i + 1, 2) = If(parts.Length > 2, parts(2).Trim(), "")
    Next

    Dim xl As Excel.Application = Nothing
    Dim wb As Excel.Workbook = Nothing
    Dim ws As Excel.Worksheet = Nothing
    Dim target As Excel.Range = Nothing

    Try
        xl = New Excel.Application()
        wb = xl.Workbooks.Add()
        ws = CType(wb.ActiveSheet, Excel.Worksheet)
        target = ws.Range("A1").Resize(rows + 1, 3)
        target.Value2 = data  ' bulk write
        xl.Visible = True     ' or wb.SaveAs("C:\contacts.xlsx")
    Finally
        If target IsNot Nothing Then Marshal.ReleaseComObject(target)
        If ws IsNot Nothing Then Marshal.ReleaseComObject(ws)
        If wb Is Not Nothing Then Marshal.ReleaseComObject(wb)
        If xl IsNot Nothing Then xl.Quit() : Marshal.ReleaseComObject(xl)
    End Try
End Sub

A few tips tied to questions above:

: if Workbooks.Add() throws COMException, ensure Excel is actually installed and available to your process; Interop automates the desktop app. See Microsoft’s Office interop walkthrough prerequisites. How to access Office interop objects.
For ASP.NET (your thread tag), do not automate Excel on the server. Use CSV or the Open XML formats instead; Microsoft does not support server‑side Office automation. Considerations for server-side Automation of Office.
The bulk write works because Excel ranges accept 2‑D arrays directly. Range.Value property.

All 16 Replies

Jx_Man 987 Nearly a Senior Poster

18 Years Ago

Go to Project -> References -> Microsoft Excel 10.0 Object Library
Then add this following code :

Dim MsExcel As Excel.Application
Private Sub Command1_Click()
MsExcel.Workbooks.Add
MsExcel.Range("A1").Value = List1.List(0)
MsExcel.Range("B1").Value = List1.List(1)
MsExcel.Range("C1").Value = List1.List(2)
MsExcel.Visible = True
End Sub

Private Sub Command2_Click()
With List1
    .AddItem (txtName.Text)
    .AddItem (txtEmail.Text)
    .AddItem (txtMobile.Text)
End With
End Sub

Private Sub Form_Load()
Set MsExcel = CreateObject("Excel.Application")
End Sub

Jade_me commented: This post help me... +1

Naruse commented: wonderful code +1

Jx_Man 987 Nearly a Senior Poster

18 Years Ago

oh... i m so sorry...
yeah that codes for vb 6, but there are same code for vb.net.
this following code for vb.net.

Dim MsExcel As Excel.Application

    Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        MsExcel = CreateObject("Excel.Application")
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        With ListBox1.Items
            .Add(txtName.Text)
            .Add(txtEmail.Text)
            .Add(txtMobile.Text)
        End With
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        MsExcel.Workbooks.Add()
        MsExcel.Range("A1").Value = txtName.Text
        MsExcel.Range("B1").Value = txtEmail.Text
        MsExcel.Range("C1").Value = txtMobile.Text
        MsExcel.Visible = True
    End Sub

- button 1 for add item from textbox to listbox.
- button 2 to add all item in listbox to excel.
- Declare Dim MsExcel As Excel.Application in top of all event. don't declare it on event cause it be a global variable.
- You can modified it as u needed.

majestic0110 commented: Excellent work. Excellent poster! +2

Estella commented: Excellent +1

majestic0110 187 Nearly a Posting Virtuoso

18 Years Ago

Jx_man probably knows it off the top of his head. He is the Don!

Jx_Man commented: Thank you +7

majestic0110 187 Nearly a Posting Virtuoso

18 Years Ago

My advice is stick with it and study. Nobody ever became a code guru overnight :-) Practice practice practice. Oh and some good tutorials will help too.

Here is one I found that looks promising.

http://www.homeandlearn.co.uk/NET/vbNet.html

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

sonia sardana -13 Posting Whiz · Answer 1 · 2008-05-11T13:32:05+00:00

HI Jx_Man, I want the code in VB.net ,but its a Vb Code...Rite????????

i do the foll. steps--
Go to project-->Add Refernce->Microsoft Excel 11.0 Object Library

cz there is noo Microsoft Excel 10.0 Object Library in Vb.net

Plz tell me y there are two commands buttons in the code given as above--
I want just the one command button Print.

Where I have to write the above steps......on print_click

My code is as under---
Public Class Form2

Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
ListBox1.Items.Add(Me.txtname.Text & " " & Me.txtmail.Text & " " & Me.txtmob.Text & " " & Date.Now)
End Sub
Dim MsExcel As Excel.Application
Private Sub btnprint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnprint.Click

End Sub
End Class

sonia sardana -13 Posting Whiz · Answer 2 · 2008-05-11T21:37:05+00:00

hi Jx_Man,THX VERY VERY MUCH.
Hey if want also that when the record goes to excel,individual columns have name also like name,email,mobile.
then?????

Hey tell me one thing if u want,how u know so much of coding...like this question......this coding u done it by urself..........or thru book. Hey plz tell me also na,i will be very thankfull to you.

sonia sardana -13 Posting Whiz · Answer 3 · 2008-05-12T01:44:55+00:00

ya he is,but i want only some tips so that i may be ............

Jx_Man 987 Nearly a Senior Poster Featured Poster · Answer 4 · 2008-05-12T10:03:51+00:00

i learn from many resource friend.
from books (i have many books), internet (google), Asking from anybody and trying it by myself. like my signature Never tried Never Know :)
Actually this forum is a great place to learn, many people with different experience will give u a many example in different type.
Microsoft supplied MSDN, looks for function that u dont know there.

sonia sardana -13 Posting Whiz · Answer 5 · 2008-05-12T10:10:10+00:00

U gave me tips thx,but u do not reply to me answer frnd.

Jx_Man 987 Nearly a Senior Poster Featured Poster · Answer 6 · 2008-05-12T10:37:28+00:00

you means :
Name (col1) | Email (col2) | Mobile (col3)
Jx_Man email 1234 (row1)
Name1 email1 4321 (row2)

MsExcel.Range("A1").Value = txtName.Text -> first Row in First Column (A)
MsExcel.Range("A2").Value = txtName.Text -> Second row in First Column (A)

sonia sardana -13 Posting Whiz · Answer 7 · 2008-05-12T18:09:01+00:00

I mean --
When data is exported to Excel,it is in the foll. format-

A1 | B1 |C1
Sonia 9881111

in excel Column names are A1,B1,c1.

I want when we click on PRINT buttin,it appears in the foll. format in excel--
Name | Mail | Mobile
Sonia 9811111

Jx_Man 987 Nearly a Senior Poster Featured Poster · Answer 8 · 2008-05-12T18:33:08+00:00

MsExcel.Range("A1").Value = "Name"
MsExcel.Range("B1").Value = "Email"
MsExcel.Range("C1").Value = "Mobile"
MsExcel.Range("A2").Value = txtName.Text
MsExcel.Range("B2").Value = txtEmail.Text
MsExcel.Range("C2").Value = txtMobile.Text

sonia sardana -13 Posting Whiz · Answer 9 · 2008-05-13T00:49:04+00:00

sonia sardana -13 Posting Whiz

18 Years Ago

THX Very Much again from my heart.

Jx_Man 987 Nearly a Senior Poster Featured Poster · Answer 10 · 2008-05-13T06:38:23+00:00

Jx_Man 987 Nearly a Senior Poster

18 Years Ago

you're welcome

jass.yip 0 Newbie Poster · Answer 11 · 2013-01-16T21:24:24+00:00

Hi, I'm very fresh in VB.

I have already try with the coding above.
but I'm facing a problem when Run it,

It show that,
MsExcel.Workbooks.Add() ---> COMException was unhandled

Is that im missing any step?

thanks.

score 0 · Answer 12 · 2013-01-17T03:33:12+00:00

Do not hijack threads, especially old ones. Please check the dates on posts before you respond to them This thread is more than four years old and it is likely that the original poster (OP) no longer needs help with this issue. Reviving this thread pushes more recent threads further down the list. If you continue to revive old threads you may be hit with an infraction.

Export Data from listbox to Excel

Dani AI

Recommended Answers Collapse Answers

All 16 Replies

Recommended Answers