Copy Excel files from sub-folders but restricted to one level

[michael.beckinsale]

Hi All,

Using 'FileSystemObject' i would like to copy all the Excel files from
the sub-folders to another directory but restricting those sub folders
to one level. Additionally l would like to exclude some specifically
named sub-folders at level one.

Example:

C:\MyName\MyFiles\ThisMonth
C:\MyName\MyFiles\ThisMonth\Sub-Folder1
C:\MyName\MyFiles\ThisMonth\Sub-Folder2\more folders
C:\MyName\MyFiles\ThisMonth\Sub-Folder3\more folders
C:\MyName\MyFiles\ThisMonth\Sub-FolderIgnore1
C:\MyName\MyFiles\ThisMonth\Sub-FolderIgnore2

Using the directory structure above as an example l would like to copy
the Excel files that exist in Sub-Folder1, Sub-Folder2 & Sub-Folder3
only.

Any example code gratefully appreciated.

Regards

Michael

 

[Jim Cone]

Something structured like the following should work...
'--
Sub Pretend()
Dim strPath As String
Dim oFSO As Object
Dim oFolder As Object
Dim vPaths As Variant
Dim N As Long

vPaths = Array(folder1_Path, folder2_Path, folder3_Path)
Set oFSO = CreateObject("Scripting.FileSystemObject")

For N = 0 To UBound(vPaths)
strPath = vPaths(N)
Set oFolder = oFSO.GetFolder(strPath)
'lots of code here
Next 'N
End Sub
--
Jim Cone
Portland, Oregon USA



"michael.beckinsale"
<[email protected]>
wrote in message
Hi All,
Using 'FileSystemObject' i would like to copy all the Excel files from
the sub-folders to another directory but restricting those sub folders
to one level. Additionally l would like to exclude some specifically
named sub-folders at level one.

Example:

C:\MyName\MyFiles\ThisMonth
C:\MyName\MyFiles\ThisMonth\Sub-Folder1
C:\MyName\MyFiles\ThisMonth\Sub-Folder2\more folders
C:\MyName\MyFiles\ThisMonth\Sub-Folder3\more folders
C:\MyName\MyFiles\ThisMonth\Sub-FolderIgnore1
C:\MyName\MyFiles\ThisMonth\Sub-FolderIgnore2

Using the directory structure above as an example l would like to copy
the Excel files that exist in Sub-Folder1, Sub-Folder2 & Sub-Folder3
only.
Any example code gratefully appreciated.
Regards
Michael

 

[michael.beckinsale]

Hi Jim,

Thanks for the reply. I am either not understanding your reply or did
not explain myelf very well.

Your example seems to imply that l know the paths of the files l want
to copy and that l build an array and loop thru the array to copy
them.

Basically l have lets say the 'TargetFolder', below this are many sub-
folders 1 level down, which may have been added too or deleted. I want
to copy the Excel files from each of these sub-folders but not the sub-
folders below that level but want to exclude 2 specific folders that l
do know the name / path of. Hope this is a bit clearer.

I have used the following code to copy files where the directory
structure is simpler and hoped that l could amend to accommodate the
above.

Sub Copy_GROUP()

Dim strFromPath As String
Dim strToPath As String
Dim Fso As Object

Sheets("Control").Activate
strFromPath = ThisWorkbook.Path & "TEST_GROUP_*.xls"

strToPath = Range("tCopyDefra") <<<file path exists in this named
range

Set Fso = CreateObject("Scripting.FileSystemObject")

'Copy all of the .xls files - , False prevents overwriting of
files
Fso.CopyFile strFromPath, strToPath
Set Fso = Nothing
End Sub

Any help gratefully received

Regards

Michael

 

[egun]

Are you wanting to recreate the sub-folder structure underneath the new
folder location, or do you just want to copy ALL files found in the
sub-folders to the (single) new folder?

Eric

 

[michael.beckinsale]

Hi Eric,

At the moment just copy to a new single folder.

Recreating the sub-folder structure would be nice but at the moment l
am getting short of time.

Hope my post / explanation makes sense.

Hope you can help

Regards

Michael

 

[Jim Cone]

Michael,
Another approach...
'--
-snip-
strFromPath = ThisWorkbook.Path & "TEST_GROUP_*.xls"
Set oFolder = oFSO.GetFolder(strFromPath)
'snip
Fso.CopyFile strFromPath, strToPath

Call CopyOtherFiles(oFolder)

Set Fso = Nothing
End Sub
'--
Function CopyOtherFiles(ByRef oParentFolder As Object)
Dim oSubFolder As Object
Dim N As Long
For Each oSubFolder In oParentFolder.SubFolders
If oSubFolder.Name <> "Sludge" And _
oSubFolder.Name <> "Residue" Then
'copy files
N = N + 1
End If
If N > 3 Then Exit Function
Next 'oSubFolder
End Function
'--
I am not sure in what order the folders will be returned.
Its conceivable you might have to return them all and do
some sort of filter on the folder names.
Note: the Windows Script 5.6 help file is invaluable.
If you don't have it, get it...
http://www.microsoft.com/downloads/...48-207d-4be1-8a76-1c4099d7bbb9&DisplayLang=en
--
Jim Cone
Portland, Oregon USA




"michael.beckinsale"
<[email protected]>
wrote in message
Hi Jim,
Thanks for the reply. I am either not understanding your reply or did
not explain myelf very well.
Your example seems to imply that l know the paths of the files l want
to copy and that l build an array and loop thru the array to copy
them.
Basically l have lets say the 'TargetFolder', below this are many sub-
folders 1 level down, which may have been added too or deleted. I want
to copy the Excel files from each of these sub-folders but not the sub-
folders below that level but want to exclude 2 specific folders that l
do know the name / path of. Hope this is a bit clearer.
I have used the following code to copy files where the directory
structure is simpler and hoped that l could amend to accommodate the
above.

Sub Copy_GROUP()

Dim strFromPath As String
Dim strToPath As String
Dim Fso As Object

Sheets("Control").Activate
strFromPath = ThisWorkbook.Path & "TEST_GROUP_*.xls"

strToPath = Range("tCopyDefra") <<<file path exists in this named
range

Set Fso = CreateObject("Scripting.FileSystemObject")

'Copy all of the .xls files - , False prevents overwriting of
files
Fso.CopyFile strFromPath, strToPath
Set Fso = Nothing
End Sub

Any help gratefully received
Regards
Michael

 

[michael.beckinsale]

Hi Jim,

That code looks to me as if its on the right lines for what l am
trying to achieve but l suspect l will have to do quite a bit of
'playing around'

The link you gave for the Script help file should as you say prove
invaluable. I have looked for help re VBScript / Scripting in general
and there doesn't seem to be a lot of info about it. It would be great
if it could be referenced via the Object Browser.

I may not get around to using your ideas / code for a couple of days
but will repost to keep you updated.

Again many thanks for your help

Regards

Michael