Excell userform requiring boxes to be populated with filenames

[DeanP]

Would appreciate any help with this as I am more confused than ever!

I have a userform, from which I want:

(a) users to browse for a folder and the selected folder path to be placed in a text box in the userform itself (TextBox1)

then, on the click of a command button I want:

(b) a list of .xlsx file names only from the folder selected in (a) to be shown in a text box in the form (TextBox2)
(c) a list of the worksheets in each of the files in (b) to be shown in a text box in the form (TextBox3)

If it matters, textbox 2 and 3 are adjacent (2-left / 3 - right - refer attachment)


I have so many different bits of code that I am completely lost as to what to do.

[Dave]

I haven't look at your file but I think I understand what you're trying to achieve. On your userform (Userform1), place a textbox (textbox1), a command button (commandbutton1), and 2 listboxes (listbox1 and listbox2). Place this code in the userform code...

Option Explicit
Private Sub CommandButton1_Click()
Dim FSO As Object, FlDr As Object, Fl As Object
Set FSO = CreateObject("scripting.filesystemobject")
Set FlDr = FSO.GetFolder(UserForm1.TextBox1.Text)
For Each Fl In FlDr.Files
If Fl.Name Like "*.xls*" Then
UserForm1.ListBox1.AddItem Fl.Name
End If
Next Fl
Set FlDr = Nothing
Set FSO = Nothing
End Sub
Private Sub ListBox1_Click()
Dim FileNm As Object, sht As Worksheet
UserForm1.ListBox2.Clear
If UserForm1.ListBox1.ListIndex <> -1 Then
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set FileNm = Workbooks.Open(UserForm1.TextBox1.Text & "\" & UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex + 1))
For Each sht In FileNm.Sheets
UserForm1.ListBox2.AddItem sht.Name
Next sht
Workbooks(FileNm.Name).Close SaveChanges:=False
Set FileNm = Nothing
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_Initialize()
UserForm1.TextBox1.Text = GetFolder
End Sub
Function GetFolder() As String
Dim FlDr As FileDialog
Dim sItem As String
Set FlDr = Application.FileDialog(msoFileDialogFolderPicker)
With FlDr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set FlDr = Nothing
End Function
To operate...

UserForm1.Show
HTH. Dave

[Paul_Hossler]

Would appreciate any help with this as I am more confused than ever!

I have a userform, from which I want:

(a) users to browse for a folder and the selected folder path to be placed in a text box in the userform itself (TextBox1)

then, on the click of a command button I want:

(b) a list of .xlsx file names only from the folder selected in (a) to be shown in a text box in the form (TextBox2)
(c) a list of the worksheets in each of the files in (b) to be shown in a text box in the form (TextBox3)

If it matters, textbox 2 and 3 are adjacent (2-left / 3 - right - refer attachment)


I have so many different bits of code that I am completely lost as to what to do.


1. THERE IS NO NEED TO SHOUT IN YOUR TITLE

2. You don't have a MSForms-type user form; you have some formatted worksheet cells

3. TextBox is probably not the most appropriate control

I'd use a 'real' UserForm with ListBox controls

The code is in the UserForm code module, I was just using the Initialize event to test

23457

[DeanP]

Hi Paul,
Thank you so much for this. Much better than what I had before. I would need to change this code so that:



(a) The user is given a starting point and would have to browse for folders. In this case the starting point
would always be T:\\Finance\SohoMaccs\2018maccs\Monthly Reports\
(b) The worksheets to be selected will always be one of the 1st 3 in every file. It would be better if only those 3
sheets are listed as some workbooks can have up to 50 sheets.
(c) The users should be able to select multiple files and worksheets, as the next step would be to copy and merge those
worksheets into a different workbook.



Thank you again for all your help! Really appreciated.

[Paul_Hossler]

Mine was only concept code

Your A+B+C are all very do-able

Feel free to ask any questions if you need to

[DeanP]

I have no idea how to do any of it at all.
I'm guessing that the starting point has something to do with CreateObject("ShellApplication"), but the explanations I could find
online are so technical and complicated that it didn't help much.
The same applies to other points.
Where can I find a resource that will explain all of it in a plain and simple way?