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.

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" <[email protected]> wrote in message news:[email protected]...
>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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
> >