PDA

View Full Version : [SOLVED] Userform with File Selector Process not working



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.

Leith Ross
12-18-2018, 08:58 PM
Hello DeanP,

Here is one way to do it...


Sub BrowseForFiles()


Dim File As Variant
Dim fldr As Variant
Dim n As Long
Dim sItem As String

With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select All Required Folders"
.AllowMultiSelect = True
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then Exit Sub
sItem = .SelectedItems(2) ' Why only the second file?
End With

n = InStrRev(sItem, "\")
fldr = Left(sItem, n)
File = Right(sItem, Len(sItem) - n)

End Sub

DeanP
12-19-2018, 08:36 AM
Hi Leith,

Thank you for your reply. This still does not list the filepath and sheet names in the textboxes. In fact nothing happens
after files are selected from the initial file name.

Leith Ross
12-19-2018, 11:17 AM
Hello DeanP.

I just provided an example of how to separate the path from the file. Since your posted workbook contained neither ActiveX controls nor any macro code, I left the implementation of the example in your hands.

DeanP
12-20-2018, 06:44 AM
Ok gotcha. Thank you so much. Your help is greatly appreciated.