Copying files from one folder to another using vba

Question

There are some similar posts about this topic, I know. However, I have a code which is different than all codes I have seen here (when talking about this subject).

The error I am receiving is saying that the file couldn't be found. But that's kind of impossible, since I am searching for the file in the same folder I am using as SOURCE in fso.CopyFile.

So I have to fix this error and, if possible, I would like to copy the file to another folder and change the name. For example, if I have the file "Excel.xls", I would like to copy with the name "Excel_old.xls", is that possible using the code below or is it too hard that it's not worth?

This is the code:

Sub CopyFiles()
'Macro to copy all files modified yesterday

Dim n As String, msg As String, d As Date
Dim fso As Object

Set fso = CreateObject("Scripting.FileSystemObject")
Set fils = fso.GetFolder("C:\Users\Desktop\Files\").Files

'Verify all files in the folder, check the modification date and then copy 
'to another folder (named Old)
For Each fil In fils
    n = fil.Name
    d = fil.DateLastModified
    If d >= Date - 1 Then
        file = n
        'The following line is where the error occurs
        fso.CopyFile "C:\Users\Desktop\Files\file", "C:\Users\Desktop\Files\Old\file"

    End If
Next fil

End Sub

JNevill · Accepted Answer · 2014-10-07 19:40:54Z

2

This is because fso.CopyFile "C:\Users\Desktop\Files\file", "C:\Users\Desktop\Files\Old\file" is not a file... It's just a string to a dummy file from the looks of it.

If instead the line was

fso.CopyFile fil.Path, "C:\Users\Desktop\Files\Old\" & fil.name... that might work.

Updated to add:

I just attempted the following using (subbing computer username for below) and had success moving everything into a new folder:

Sub test()
    Dim fso As FileSystemObject
    Dim fsoFiles As Files
    Dim fil As File

    Set fso = New FileSystemObject
    Set fils = fso.GetFolder("C:\Users\<MY USERNAME>\Desktop\").Files

    For Each fil In fils
        n = fil.Name
        d = fil.DateLastModified
        fso.CopyFile fil.Path, fil.ParentFolder & "\test\" & fil.Name

    Next fil
End Sub

The only difference here is that I used fil.ParentFolder to get my Desktop and then tossed it into a new folder I created on my desktop (prior to running the script) named "test".

edited Oct 7, 2014 at 19:40

answered Oct 7, 2014 at 17:45

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

ZAT Over a year ago

that's right. But there is a typo, after fso.CopyFile it should be either file or fil.name according to OP's code.

JNevill Over a year ago

Yep.. should have been fil.path not fil.name. My apologies. I guess I'm a bit rusty with fso.

dekio Over a year ago

Used fso.CopyFile fil.Path, "C:\Users\Desktop\Files\file", "C:\Users\Desktop\Files\Old\file" and got the same error, Type Mismatch. I also tried fso.CopyFile fil.Path, "C:\Users\Desktop\Files\file", "C:\Users\Desktop\Files\Old\" and still getting error 13

EStraka Over a year ago

Shouldn't it be: "C:\Users\Desktop\Files\" & file, "C:\Users\Desktop\Files\Old\" & file

dekio Over a year ago

Tried, same error again, I'm searching but I'm really struggling

|

serenesat · Accepted Answer · 2015-07-15 12:03:58Z

0

For moving all files in a folder:

Sub MoveFiles()

Dim MyFile As String

MyFile = Dir("C:\AAAA\*.*")

Do Until MyFile = ""

Name "C:\AAAA\" & MyFile As "C:\AAA\" & MyFile

MyFile = Dir

Loop

End Sub

edited Jul 15, 2015 at 12:03

serenesat

4,72210 gold badges41 silver badges53 bronze badges

answered Jul 15, 2015 at 11:57

user3884205

111 bronze badge

Comments

Jeremy Morren · Accepted Answer · 2020-11-30 14:08:17Z

0

To copy all files and sub-folders recursively, use the following code:

Public Sub CopyDirectory(ByVal source As String, ByVal destination As String)
    Dim fso, file, folder As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    'Delete existing folder
    If fso.FolderExists(destination) Then fso.DeleteFolder destination, True
    fso.CreateFolder destination
    For Each file in fso.GetFolder(source).files
        fso.CopyFile file.Path, destination & "\" & file.Name
    Next file
    For Each folder in fso.GetFolder(source).SubFolders
        CopyDirectory folder.Path, destination & "\" & folder.Name
    Next folder
End Sub

Use as following:

CopyFile "C:\Path To Source", "C:\Path to destination"

Note that the paths should not include a trailing directory separator (\).

answered Nov 30, 2020 at 14:08

Jeremy Morren

7743 gold badges13 silver badges29 bronze badges

1 Comment

user8285660 Over a year ago

Potentially how would you set it up so you could check to see if the file exists (latest rev?) in the destination folder before copying it?

Collectives™ on Stack Overflow

Copying files from one folder to another using vba

3 Answers 3

8 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related