Solved: Automatically Open Files On Folder Select

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I have a directory which contains 'monthly' sub folders.

Each subfolder contain two files. The first is called "SIP", the second, "Extract".

What I'd like to do is allow the user to select a 'monthly' sub folder, then the two files ("SIP" and "Extract") to open automatically.

I'm using the code below (found through a Internet search), which opens the dialog box, allowing the to search for the 'Monthly' sub folder, but I'm unsure how to automatically open the two files.

[VBA]
Sub GetFileNameFromUser()

Dim FN As String

FN = Application.GetOpenFilename("All files (*.*), *.*")

If FN = "False" Then Exit Sub

End Sub

[/VBA]

I just wondered whether someone may be able to look at this please and offer some guidance on how I may go about this.

Many thanks and kind regards

Chris

[mancubus]

hi,

with msoFileDialogOpen

[vba]
Sub OpenMultipleFiles()

Dim NumFiles As Long

With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = "" ' or specify a directory: "C:\Files\etc\etc"
.Title = "Select File(s) To Open"
.Filters.Add "Excel Files", "*.xls*", 1
.AllowMultiSelect = True
If .Show = -1 Then
For NumFiles = 1 To .SelectedItems.Count
Workbooks.Open .SelectedItems(NumFiles)
Next NumFiles
Else
MsgBox "Cancelled!"
End If
End With

End Sub
[/vba]

[hobbiton73]

Hi @mancubus, thank you for taking the time to reply to my post and for the solution.

I've tried this, and although it allows the user to hold 'Shift' down to select mutliple files, it doesn't automatically open the files when the folder is selected, which is really what I was looking for if at all possible.

Have you any ideas where I may be going wrong please?

Many thanks and kind regards

Chris

[mancubus]

you are welcome.

so what you want is to enable the user only SELECT the folder. and all files in that folder get opened by procedure without selecting?

[vba]

Sub OpenMultipleFilesFromFolder()

Dim fPath As String, fName As String

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = "" ' or specify a directory: "C:\Files\etc\etc"
.Title = "Select a Folder To Open File(s)"
.AllowMultiSelect = False
If .Show = -1 Then
fPath = .SelectedItems(1)
Else
MsgBox "Cancelled!"
End If
End With

fName = Dir(fPath & "\*.xls*")
Do While fName <> ""
Workbooks.Open Filename:=fPath & "\" & fName
fName = Dir
Loop

End Sub
[/vba]

[hobbiton73]

Hi @mancucus, thank you very much for your continued help with this, it is greatly appreciated.

You are correct in your assumption i.e. that I'd like the files to be opened automatically

I tried the solution you kindly provided, and although I was able to select the folder to open, the code only opened the first file before displaying a '400' error.

I have however been thinking about this, and my sincere apologies for messign you around with this, but I wondered whether it would be possible to do the following:

• I hard code this file path into the VB code 'Z:\CHRIS\Work\Monthly Extracts'.
• Then, when when the user selects the macro, a dialog box will appear asking the user to type in the month.
• Then, when they've entered this information and selected 'OK', the files in that folder will automatically open.

Once again my sincere apologies for messing you around.

Many thanks and kind regards

[mancubus]

hi hobbiton73

actually i tested it before posting here and it worked for me?
are all files in the folder excel files?



tested and worked:
[vba]
Sub OpenMultipleFilesFromFolder2()

Dim fPath As String, mName As String, fName As String

fPath = "Z:\CHRIS\Work\Monthly Extracts"
mName = InputBox(Prompt:="Please Type Month Name", Title:="Month Folder")
If mName = vbNullString Then
MsgBox "Missing Month Info"
Exit Sub
End If

fPath = fPath & "\" & mName & "\"
fName = Dir(fPath & "\*.xls*")
Do While fName <> ""
Workbooks.Open Filename:=fPath & "\" & fName
fName = Dir
Loop

End Sub
[/vba]

[hobbiton73]

Hi @mancucusm this works beautifully!

Thank you so very much for all your time and trouble.

All the best and kind regards

Chris

[mancubus]

you're welcome chris.



ps: i tested and then corrected / modified one or two lines

[snb]

or:

[VBA]
Sub OpenMultipleFiles_snb()
On Error Resume Next

For Each fl In CreateObject("scripting.filesystemobject").getfolder("Z:\CHRIS\Work\Monthly Extracts\" & MonthName(InputBox("Please Type Month Number: 1 - 12"))).Files
Workbooks.Open fl
Next
End Sub
[/VBA]