asked on

Opening all files in a given folder

I have a code that opens the Open dialog for a specified folder and allows me to select and open all the files at once. Is there something simple I can add to the code below that will open all the files automatically? If not then how would I do that?

Thanks,
John

Sub OpenMultipleFiles()
Dim Filter As String, Title As String, msg As String
Dim i As Integer, FilterIndex As Integer
Dim Filename As Variant
Filter = "Excel Files (*.xls),*.xls,"
Title = "Select File(s) to Open"
ChDrive ("C")
ChDir ("C:\Documents and Settings\T0122059\Desktop\KRG1")
With Application
    Filename = .GetOpenFileName(Filter, FilterIndex, Title, , True)
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
End With
If Not IsArray(Filename) Then Exit Sub
For i = LBound(Filename) To UBound(Filename)
    Workbooks.Open Filename(i)
Next i
End Sub

Open in new window

SmittyPro

Sure you can do it, but what should be the trigger?

John Carney

ASKER

A button to call the macro.

John Carney

ASKER

In other words, rather than opening the dialog box and then manually selecting all the files and clicking Open, the macro just does it all automatically.

Thanks,
John

ASKER CERTIFIED SOLUTION

SmittyPro

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

Thanks, SmittyPro. I couldn't get it to work as is, but I fooled around with it for quite a while and for some inexplicable (to me at least) reason, this does work:

Dim myDir As String, fn As String
myDir = "H:\Depts\css\A_ILS & Reliability\Reliability\1-CURRENT\System Reporting\SDAE\JAL\3_Working Files\SourceFiles\Originals\New Weeks\"
fn = Dir(myDir & "*.xls")
Do While fn <> ""
Workbooks.Open (myDir & fn)
fn = Dir()
Loop

So thanks.
John

SmittyPro

Glad that you got it to work for you. :)