Open all files in subfolders
 

I have many level 1 subfolders with examples shown below

Country
America
Germany
Japan
France

In each subfolders are some excel files. I want to paste some values from a
master file to all the excel files found in the subfolders. Anyone can help?
Thanks.

Open all files in subfolders
 

Hi linglc

You can adapt this macro
http://www.rondebruin.nl/fso.htm

If you need help post back



--
Regards Ron de Bruin
http://www.rondebruin.nl



"linglc" wrote in message ...
I have many level 1 subfolders with examples shown below

Country
America
Germany
Japan
France

In each subfolders are some excel files. I want to paste some values from a
master file to all the excel files found in the subfolders. Anyone can help?
Thanks.

Open all files in subfolders
 

Sub LoopFolders()

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

(replace somewhere in email address with gmail if mailing direct)

"linglc" wrote in message
...
I have many level 1 subfolders with examples shown below

Country
America
Germany
Japan
France

In each subfolders are some excel files. I want to paste some values from
a
master file to all the excel files found in the subfolders. Anyone can
help?
Thanks.

Open all files in subfolders
 

Bob,

Thanks for your reply. However I have some problem with this row

Set Folder = oFSO.GetFolder(sPath)

I get the message -- run time error '424' : object required

Do you know why?


--
help needed


"Bob Phillips" wrote:


Sub LoopFolders()

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

(replace somewhere in email address with gmail if mailing direct)

"linglc" wrote in message
...
I have many level 1 subfolders with examples shown below

Country
America
Germany
Japan
France

In each subfolders are some excel files. I want to paste some values from
a
master file to all the excel files found in the subfolders. Anyone can
help?
Thanks.

Open all files in subfolders
 

Sorry, I missed one vital line.

AT the very start, before the first sub, add

Dim oFSO As Object

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"linglc" wrote in message
...
Bob,

Thanks for your reply. However I have some problem with this row

Set Folder = oFSO.GetFolder(sPath)

I get the message -- run time error '424' : object required

Do you know why?


--
help needed


"Bob Phillips" wrote:


Sub LoopFolders()

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

(replace somewhere in email address with gmail if mailing direct)

"linglc" wrote in message
...
I have many level 1 subfolders with examples shown below

Country
America
Germany
Japan
France

In each subfolders are some excel files. I want to paste some values
from
a
master file to all the excel files found in the subfolders. Anyone can
help?
Thanks.