Code to import data based on selection in two listboxes

[Steven Taylor]

Hi All,



I would appreciate some help with the following:



1. I have a user form with two list boxes.

2. The first list box displays the name of all open excel workbooks.

3. The second list box will display the sheets of the workbook the user
selects

from the first list box.

4. I would like to add an import button to my user form.

5. The import button will import the data from the workbook selected in

the first list box and from the sheet selected in the second list box into

sheet1 of the template workbook where the user form is displayed.



All comments and suggestion welcome.



Thanks,



Steve



PS I am using excel 2002 and windows XP.

 

[Guest]

Hi,

Try to add these following codes to your prject:

'1. I have a user form with two list boxes.
Sub RetreiveShtName(BookSource As Workbook, _
BookDestination As Workbook)
Dim Wks As Worksheet

'2. The first list box displays the name of all open excel workbooks.
For Each Wks In BookSource.Sheets
ListBox1.AddItem BookSource.Name & "-" & Wks.Name
Next Wks

'3. The second list box will display the sheets of the workbook
'the user selects
For Each Wks In BookDestination.Sheets
ListBox2.AddItem BookDestination.Name & "-" & Wks.Name
Next Wks

'to avoid Null for listbox value that error may occured when
'commandbutton1_click (if null)
ListBox1.ListIndex = 0
ListBox2.ListIndex = 0

End Sub

'4. I would like to add an import button to my user form.
Sub RetreiveData(dSource As Range, dDest As Range)
dSource.Copy Destination:=dDest
End Sub

'5. The import button will import the data from the workbook
'selected in
'the first list box and from the sheet selected in the second list
'box into
'sheet1 of the template workbook where the user form is displayed.

Private Sub CommandButton1_Click()
Dim SpltName1 As Variant
Dim SpltName2 As Variant

SpltName1 = Split(ListBox1, "-")
SpltName2 = Split(ListBox2, "-")

RetreiveData _
Workbooks(SpltName1(0)).Sheets(SpltName1(1)).Range("A1"), _
Workbooks(SpltName2(0)).Sheets(SpltName2(1)).Range("A1")

End Sub

Private Sub UserForm_Initialize()
RetreiveShtName Workbooks("book1.xls"), Workbooks("book2.xls")
End Sub

I hope the help you...