DeanP
12-17-2018, 01:18 PM
Users need to browse for and select files from which certain worksheets have to be copied to another location,
and all of the worksheets combined into one worksheet.
In my active worksheet I have an active x control button that will run the code to do all the above when clicked.
I have created a userform that I've called the FILE SELECTOR TOOL that needs to pop-up when the control button is
clicked. In the there users will have to tell the macro what to do. (Refer attached sample of the form)
(a) click a browse button that will enable browsing in the directory where the files are located
(b) one a file is selected, the macro need to (preferably without opening the workbook) show a list in the selector
tool of the names of all the sheets in the workbook
(c) I want the users to be able to select the sheets that they want to be copied by highlighting them
(d) click on a command button "ok" will then copy the sheets to a location that is confirmed by the user following
a prompt
So far I have the code blow that enables the selection of the files. However, I need to refine it to seperate the
file path and copy that in one box and list the sheets in that file in the next box - and that is where I am stuck
- I can't find how to do that.
Function GetFolder() As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFilePicker)
With fldr
.Title = "Select All Required Folders"
.AllowMultiSelect = True
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(2)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function
Any help would be greatly appreciated.
and all of the worksheets combined into one worksheet.
In my active worksheet I have an active x control button that will run the code to do all the above when clicked.
I have created a userform that I've called the FILE SELECTOR TOOL that needs to pop-up when the control button is
clicked. In the there users will have to tell the macro what to do. (Refer attached sample of the form)
(a) click a browse button that will enable browsing in the directory where the files are located
(b) one a file is selected, the macro need to (preferably without opening the workbook) show a list in the selector
tool of the names of all the sheets in the workbook
(c) I want the users to be able to select the sheets that they want to be copied by highlighting them
(d) click on a command button "ok" will then copy the sheets to a location that is confirmed by the user following
a prompt
So far I have the code blow that enables the selection of the files. However, I need to refine it to seperate the
file path and copy that in one box and list the sheets in that file in the next box - and that is where I am stuck
- I can't find how to do that.
Function GetFolder() As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFilePicker)
With fldr
.Title = "Select All Required Folders"
.AllowMultiSelect = True
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(2)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function
Any help would be greatly appreciated.