Copy all files from one folder to another in Access

[Pat]

I note that the Filecopy command does not allow for wildcards.
Seems i am up for a Dir(SceDir & "\*.*) and do a loop including Filecopy with each filename Dir returns.

Is there an easier way? I don't want to use the Shell command as this may change with upgrades to windows.

[HansV]

Would it be feasible to use Scripting?

Code: Select all

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile SceDir & "\*.*", "C:\OtherFolder\"
Set fso = Nothing

or

Code: Select all

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFolder SceDir, "C:\OtherFolder\"
Set fso = Nothing

[agibsonsw]

I found the following code which uses the FileSystemObject's CopyFile method which does allow wildcards.

Code: Select all

Sub CopyFilesFolder2Folder()
Dim fso
Dim sfol As String, dfol As String
sfol = "c:\MyFolder" ' change to match the source folder path
dfol = "e:\MyFolder" ' change to match the destination folder path
Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
If Not fso.FolderExists(sfol) Then
    MsgBox sfol & " is not a valid folder/path.", vbInformation, "Invalid Source"
ElseIf Not fso.FolderExists(dfol) Then
    MsgBox dfol & " is not a valid folder/path.", vbInformation, "Invalid Destination"
Else
    fso.CopyFile (sfol & "\*.*"), dfol ' Change "\*.*" to "\*.xls" to move Excel Files only
End If
If Err.Number = 53 Then MsgBox "File not found"
End Sub

[Pat]

Would it be feasible to use Scripting?

I will try this out, thank you.

Just tried, it works well and doesn't require a reference.