filesystemobject

[Hemant_india]

hi guys
i want to copy a particular file from each folder of "e:" drive
how am i to loop each folder from a drive
(Drive is remote drive ---cd-rom)
please help ... i am all blank at present

 

[JLGWhiz]

Take a look at this site for some guidelines.

http://www.ozgrid.com/VBA/loop-through.htm

 

[Chip Pearson]

If you want to search through only the top level directories under the E:
root, use

Sub CopyTheFiles()
Dim FSO As Scripting.FileSystemObject
Dim RD As Scripting.Folder
Dim FF As Scripting.Folder
Dim F As Scripting.File


Set FSO = New Scripting.FileSystemObject
Set RD = FSO.GetDrive("E").RootFolder
For Each FF In RD.SubFolders
Set F = FF.Files("TheFileName.xls") '<<< CHANGE FILE NAME
F.Copy Destination:="C:\Whatever\" & F.Name '<<< CHANGE FOLDER
Next FF
End Sub

If you want to look through EVERY folder and subfolder under the E: root,
use

Sub CopyTheFiles()
Dim FSO As Scripting.FileSystemObject
Dim RD As Scripting.Folder
Dim FF As Scripting.Folder
Dim F As Scripting.File

Set FSO = New Scripting.FileSystemObject
Set RD = FSO.GetDrive("G").RootFolder
For Each FF In RD.SubFolders
DoOneFolder WhatFolder:=FF
Next FF
End Sub

Sub DoOneFolder(WhatFolder As Scripting.Folder)
Dim F As Scripting.File
Dim FF As Scripting.Folder
Set F = WhatFolder.Files("TheFileName.xls")
F.Copy Destination:="C:\Whatever\" & F.Name

For Each FF In WhatFolder.SubFolders
DoOneFolder WhatFolder:=FF
Next FF
End Sub

See also http://www.cpearson.com/Excel/RecursionAndFSO.htm for example code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Hemant_india]

thank both of you sir(s)
after i posted my query i gave a try using dir() function
it is also working
but i am going to use chip's code. The only thing i have to do is
to add a referance . am i right?
thanks again