Excel VBA - Copy Folder problem

[PaulC]

I wish to copy a folder and its sub-folders and all its files to anothe
location from within an Excel VBA macro. Is this possible?

Pau

 

[Bob Phillips]

Hi Paul,

All things are possible!

Dim FSO As Object

Sub Folders()
Dim i As Long
Dim sFolder As String
Dim sSource As String
Dim sTarget As String

sSource = "C:\MyTest"
sTarget = "C:\NewDir"

Set FSO = CreateObject("Scripting.FileSystemObject")

On Error Resume Next
If FSO.GetFolder(sTarget) Is Nothing Then
MkDir sTarget
End If
On Error GoTo 0

CopyFiles sSource, sTarget

End Sub

'-----------------------------------------------------------------------
Sub CopyFiles(ByVal Source As String, ByVal Target As String)
'-----------------------------------------------------------------------
Dim oFldr As Object
Dim oFolder As Object
Dim oFile As Object
Dim oFiles As Object
Dim sTarget As String

Set oFolder = FSO.GetFolder(Source)
If InStr(4, oFolder.Path, "\") = 0 Then
sTarget = Target
Else
sTarget = Target & Mid(Source, InStr(4, oFolder.Path, "\"), 255)
End If
On Error Resume Next
If FSO.GetFolder(sTarget) Is Nothing Then
MkDir sTarget
End If
On Error GoTo 0
Set oFolder = FSO.GetFolder(Source)
Set oFiles = oFolder.Files
For Each oFile In oFiles
oFile.Copy (sTarget & "\" & oFile.Name)
Next oFile

For Each oFldr In oFolder.Subfolders
CopyFiles oFldr.Path, Target
Next

End Sub

--

HTH

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

 

[Jim Rech]

You can duplicate a complete folder like this:

Sub CopyFolder()
Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject
fso.CopyFolder "c:\old", "c:\new"
End Sub


--
Jim Rech
Excel MVP
|I wish to copy a folder and its sub-folders and all its files to another
| location from within an Excel VBA macro. Is this possible?
|
| Paul
|
|
| ---
| Message posted
|

 

[PaulC]

Many thanks Bob,

Your copy folder macro works perfectly, with the advantage that it wil
create a new folder if its not already there. I have made use of thi
so that I can create a new one for each day with the date at the end o
the folder name.

Thanks again,

Pau

 

[michael mclane]

Greetings...and TIA for your help.
Is it possible to exclude a specific file (eg "test.xls") from this
code?I'm not able to get the NAME of the file that is being
processing...any idea?
please help me!!

 

[Dave Peterson]

The thread has aged off my cache of messages, but in general:

if lcase(somevariableforthefilename) = "test.xls" then
'do nothing
else
'do all the stuff you want to do
end if