FileCopy File Exists

  • Good afternoon Experts

    I need a little help please.

    I am using VBA to Copy a file from one location to another. All works fine, except...

    On occasions the file may already exist in the new location, so I need to put a "1" on the end of the file (and if necessary increment)

    The code I have is as follows:

    I look forward to your responses.

  • Something like this perhaps?

    Best Regards,
    Luke M
    "A little knowledge is a dangerous thing."

  • Thanks Luke M

    That works, however there is a slight error (and is likely my fault in not giving a bit more code.

    After your new code, I also have the following code:

    MyFIle = Dir

    It is now this line that fails.

    Basically I am looping through lots of files and once dealt with I move them to the new destination and continue to the next MyFile.

    Also, how do I check to see if the file exists (in the new code) (in the new destination) and if not just copy the file.

    For ease my current code is:

  • Ok... I am almost there, I understand that the

    Dir(DestPath & StrTemp)

    is resetting the original Dir.

    It appears there is a workround if I use

    DIM fso as object
    set fso = createobject("scripting.filesystemobject")
    fso.copyfile (myfile, destpath)

    just not sure if the fso.copyfile line is the correct code.

    If I use this would it be better to use

    fso.movefile(myfile, destpath)

    if so are they the correct parameters?

  • IT depends on what your goal is. FileCopy() overwrites the Target if it exists, You can do the same with fso.CopyFile( Source, Target, True).

    FSO also has FileExists() so you can check if needed.

    Dir() loop is often used to find with wildcard searching just one folder. If you want to search for subfolders, there are FSO ways and what I use, a command shell DIr() method. My method can be used for single folders as well. It puts the full names into an array which is easily iterated.

  • Thank you Kenneth.

    What I would like it to open a file do stuff with that, close the file, and then move the file to a new location, but first I need to check if that file exists in the new location. If it does I need to rename the file I have opened with the next number on (1 or 2 etc) and then move to the new location.

    I had been using


    FileCopy MyPath & MyFile, DestPath & MyFile 'copy file to new location
            Kill MyPath & MyFile 'code to delete origianl MyFile
    Application.DisplayAlerts = True
        End If
    MyFile = Dir

    but with the renaming I used:

    But realised that the Dir gets restated, and so the "MyFile = Dir" line fails.
    So can you please help me as to how I can use filesystemobject for the new destination path, and still rename the MyFile if it exists in the new location.

    I have not use fso before, so any help will be appreciated.

    Hope this makes sense, as I have lots of code inbetween opening the file and moving it.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!