Export date from listbox to Excel - Xtreme Visual Basic Talk

03-24-2003, 07:51 AM

Elik

Regular

Join Date: Mar 2003

Posts: 69

Export date from listbox to Excel

Could anyone help me to export date from listbox to Excel

Please help me!

03-24-2003, 09:06 AM

Wamphyri

Variable not defined

Retired Moderator
* Guru *

Join Date: Apr 2002

Location: Ottawa, Ontario

Posts: 4,793

Is this a listbox in a userform in excel or a listbox in a form outside of excel?

__________________
-Carl

03-24-2003, 09:26 AM

Elik

Regular

Join Date: Mar 2003

Posts: 69

Quote:
Originally Posted by Wamphyri
Is this a listbox in a userform in excel or a listbox in a form outside of excel?

Listbox in a form outside of excel.
I need from within vb to export to excel some date which are in List1 and List2 bt never done something like this

Thanks in advance !

03-24-2003, 10:12 AM

Wamphyri

Variable not defined

Retired Moderator
* Guru *

Join Date: Apr 2002

Location: Ottawa, Ontario

Posts: 4,793

I'm assuming that you've never exported anything to Excel before.
So First thing. Add a reference to Microsoft's Excel Object Library.
Goto Projects -> References and choose Microsoft's Excel Object Library.

Enter the following code.

Code:

Dim xlApp As Excel.Application
Dim xlSh As Excel.Worksheet
Dim i As Long
Set xlApp = New Excel.Application

xlApp.Visible = True
xlApp.Workbooks.Add
Set xlSh = xlApp.Workbooks(1).Worksheets(1)
For i = 1 To List1.ListCount
    xlSh.cells(i, 1).Value = List1.List(i - 1)
Next

Set xlSh = Nothing
Set xlApp = Nothing

__________________
-Carl

03-24-2003, 10:56 AM

Elik

Regular

Join Date: Mar 2003

Posts: 69

Quote:
Originally Posted by Wamphyri
I'm assuming that you've never exported anything to Excel before.
So First thing. Add a reference to Microsoft's Excel Object Library.
Goto Projects -> References and choose Microsoft's Excel Object Library.

Enter the following code.

Code:

Dim xlApp As Excel.Application Dim xlSh As Excel.Worksheet Dim i As Long Set xlApp = New Excel.Application xlApp.Visible = True xlApp.Workbooks.Add Set xlSh = xlApp.Workbooks(1).Worksheets(1) For i = 1 To List1.ListCount xlSh.cells(i, 1).Value = List1.List(i - 1) Next Set xlSh = Nothing Set xlApp = Nothing

Tank you Wamphyri this code runs fine
But How could I integrate the second Listbox(List2) here

Tanks in advance

03-24-2003, 11:17 AM

Elik

Regular

Join Date: Mar 2003

Posts: 69

I have added the second list box Like bellow is it a righte decision?

Code:

Private Sub Command3_Click()
Dim xlApp As Excel.Application
Dim xlSh As Excel.Worksheet
Dim i As Long
Dim y As Long

Set xlApp = New Excel.Application

xlApp.Visible = True
xlApp.Workbooks.Add
Set xlSh = xlApp.Workbooks(1).Worksheets(1)

For i = 1 To List1.ListCount

    xlSh.Cells(i, 1).Value = List1.List(i - 1)
    
Next

       For y = 1 To List2.ListCount
       
          xlSh.Cells(y, 2).Value = List2.List(y - 1)
          
         Next

Set xlSh = Nothing
Set xlApp = Nothing

End Sub

Last edited by Wamphyri; 03-24-2003 at 12:44 PM. Reason: Adding [vb] tags

03-24-2003, 12:46 PM

Wamphyri

Variable not defined

Retired Moderator
* Guru *

Join Date: Apr 2002

Location: Ottawa, Ontario

Posts: 4,793

Looks ok. The real test however is does it work the way you want it to?

__________________
-Carl

03-24-2003, 10:21 PM

Elik

Regular

Join Date: Mar 2003

Posts: 69

Quote:
Originally Posted by Wamphyri
Looks ok. The real test however is does it work the way you want it to?

Oh! There is no problem with this code!

07-11-2003, 10:23 AM

erbartle

Newcomer

Join Date: Jun 2003

Posts: 11

ListBox Related question

I have a form that contains a combobox and a list box. i want the contents of the listbox to change depending on what is selected in the combobox. I would want the listbox to be inactive (which I dont know how to do yet) untill a selection was made in the combobox. then at this point depending on what choice was made in the combobox a particular list would load into the listbox. what is the best way to do this?

Thanks a lot for the help.

Last edited by Wamphyri; 07-11-2003 at 10:34 AM. Reason: removed quote

#10

07-11-2003, 10:34 AM

Wamphyri

Variable not defined

Retired Moderator
* Guru *

Join Date: Apr 2002

Location: Ottawa, Ontario

Posts: 4,793

The listbox you can set to Enabled = False initially
As for the listbox changing based on the selection in the combobox you can use a Select Case statement in the Change event of the Combobox
A small example

Code:

Private Sub ComboBox1_Change()
    ListBox1.Enabled = True
    Select Case ComboBox1.Text
        Case "Choice1"
            ListBox1.Clear
            ListBox1.AddItem "This"
            ListBox1.AddItem "That"
        Case "Choice2"
            ListBox1.Clear
            ListBox1.AddItem "Cat"
            ListBox1.AddItem "Dog"
    End Select
End Sub

__________________
-Carl

#11

07-11-2003, 10:49 AM

erbartle

Newcomer

Join Date: Jun 2003

Posts: 11

thanks a lot for the help.

is there anyway to pass variabes, in the case arrays, along with the form. when i initialized i put a bunch of values from a sheet into arrays in order to put them in the listbox and combobox. i want to pass them from the initializing routine to the combobox_change routine

Quote:
Originally Posted by Wamphyri
The listbox you can set to Enabled = False initially
As for the listbox changing based on the selection in the combobox you can use a Select Case statement in the Change event of the Combobox
A small example

Code:

Private Sub ComboBox1_Change() ListBox1.Enabled = True Select Case ComboBox1.Text Case "Choice1" ListBox1.Clear ListBox1.AddItem "This" ListBox1.AddItem "That" Case "Choice2" ListBox1.Clear ListBox1.AddItem "Cat" ListBox1.AddItem "Dog" End Select End Sub