Export Data from listbox to Excel

[sonia sardana]

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.

[Jx_Man]

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

[sonia sardana]

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

[Jx_Man]

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.

[sonia sardana]

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.

[majestic0110]

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

[sonia sardana]

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

[majestic0110]

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

[Jx_Man]

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]

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

[Jx_Man]

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]

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

A1 | B1 |C1
Sonia [e-mail] 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 [e-mail] 9811111

[Jx_Man]

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]

THX Very Much again from my heart.

[Jx_Man]

you're welcome

[jass.yip]

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.

[Reverend Jim]

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.