Need to copy Worksheets from a file choosen in a ListBox

Emilio92

New Member
Joined
Jan 3, 2018
Messages
3
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top