Need to copy Worksheets from a file choosen in a ListBox

[Emilio92]

Hi,

I need to get further with this assignment. With this code I've found a given data file in my ListBox with search in the folder of my main workbook location:

Public Sub UserForm_Activate()
Dim fso As FileSystemObject
Dim dir As Folder
Dim file As file

Set fso = New FileSystemObject
Set dir = fso.GetFolder(ThisWorkbook.Path & Application.PathSeparator & "Datafiles")
For Each file In dir.Files
If file.Name Like "*" & ".xlsx" Then
ListBox.AddItem (file.Name)
End If
Next
End Sub

But now I need to copy all three Worksheets from the given/chosen datafile into my Main Workbook. How can I do this? It should still be a non local folder, since it should be used in other computers than my own. I havn't made the "Public Sub ListBox_Click ()" yet, since I would wait until I had a solution by one of you nice guys.

Best regards, Emilio

 

[NdNoviceHlp]

The last part is confusing. Here's some code to get U started. It copies "Sheet1" of the selected file in Listbox1 of Userform1 to the 2nd sheet of the active workbook. HTH. Dave

Option Explicit

Private Sub UserForm_Activate()
Dim fso As Object, FolDir As Object, File As Object
Set fso = CreateObject("scripting.filesystemobject")
Set FolDir = fso.GetFolder(ThisWorkbook.Path & "\Datafiles")
For Each File In FolDir.Files
If File.Name Like "*" & ".xlsx" Then
UserForm1.ListBox1.AddItem (File.Name)
End If
Next
Set FolDir = Nothing
Set fso = Nothing
End Sub

Private Sub ListBox1_Click()
Workbooks.Open Filename:=ThisWorkbook.Path & "\Datafiles\" & UserForm1.ListBox1.Text
Sheets("Sheet1").Copy After:=ThisWorkbook.Sheets(1)
Application.DisplayAlerts = False
Workbooks(UserForm1.ListBox1.Text).Close SaveChanges:=False
End Sub