Opening Multiple files in separate Excel folders

[Paul Dunn]

Can this be done?

 

[Frank Kabel]

Hi
what do you mean with 'separate Excel folders'?

 

[Bob Phillips]

Yes.


Dim aryFiles
Dim oFSO

Sub LoopFolders()
Dim i As Integer

Set oFSO = CreateObject("Scripting.FileSystemObject")

selectFiles "c:\MyTest"

Set oFSO = Nothing

End Sub


'---------------------------------------------------------------------------
Sub selectFiles(sPath)
'---------------------------------------------------------------------------
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr

Set Folder = oFSO.GetFolder(sPath)

For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr

For Each file In Folder.Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
End If
Next file

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Paul DunN]

I have separate folders set up for each customer. In
those folders are the files. I would like to open three
of my customers files without going to each folder and
opening one, then going to the next one and opening it,
etc. I've tried going to "My Computer" and highliting
each folder, but only the folders open, not Excel.

Thanks

 

[Dave Peterson]

If it were important to me, I'd consider making a "master" workbook with a
worksheet that contains hyperlinks to each of the workbooks that I need to open.

=hyperlink("c:\my documents\excel\book1.xls")

If I had to open a bunch of them all the time, I'd put them in a list (say
A1:Axx of Sheet1).

And run a macro:

Option Explicit
Sub SaveACopy()

Dim myRng As Range
Dim myCell As Range
Dim testStr As String

With Worksheets("sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In myRng.Cells
If IsEmpty(myCell) Then
'do nothing
Else
testStr = ""
On Error Resume Next
testStr = Dir(myCell.Value)
On Error GoTo 0
If testStr = "" Then
MsgBox myCell.Value & " Couldn't be opened"
Else
Workbooks.Open Filename:=myCell.Value
End If
End If
Next myCell
End With

End Sub

(The info in A1:Axx contains the full path and filename.)