FileCopy File Exists

mar050703 · Oct 25th 2017

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:

Code

Dim MyPath As String
Dim DestPath As String
Dim MyFile As String
Dim MyExt As String
Dim wb As Workbook
Dim NewFile As String




MyPath = "C:\Users\Owner\Dropbox\Accounts Miqlat\"
DestPath = "C:\Users\Owner\Dropbox\Accounts Miqlat\Imported\" 'Destination to move completed file too
MyExt = "*.xlsx"
MyFile = Dir(MyPath & MyExt)


FileCopy MyPath & MyFile, DestPath & MyFile 'copy file to new location

Display More

I look forward to your responses.

**Luke M** · Oct 25th 2017

Something like this perhaps?

Code

Dim MyPath As String
Dim DestPath As String
Dim MyFile As String
Dim MyExt As String
Dim wb As Workbook
Dim NewFile As String
Dim strCheck As String
Dim i As Long
Dim strTemp As String




MyPath = "C:\Users\Owner\Dropbox\Accounts Miqlat\"
DestPath = "C:\Users\Owner\Dropbox\Accounts Miqlat\Imported\" 'Destination to move completed file too
MyExt = "*.xlsx"
MyFile = Dir(MyPath & MyExt)


'Check if file exists already
strTemp = MyFile
strCheck = Dir(DestPath & MyFile)
Do Until strCheck = ""
    i = i + 1
    strTemp = Replace(MyFile, Mid(MyExt, 2), "(" & i & ")" & Mid(MyExt, 2))
    strCheck = Dir(DestPath & strTemp)
Loop
FileCopy MyPath & MyFile, DestPath & strTemp 'copy file to new location

Display More

mar050703 · Oct 28th 2017

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:

Code

MyFIle = Dir
loop

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:

Code

Dim MyPath As String
Dim DestPath As String
Dim MyFile As String
Dim MyExt As String
Dim wb As Workbook
Dim NewFile As String




MyPath = "C:\Users\Owner\Dropbox\Accounts Miqlat\"
DestPath = "C:\Users\Owner\Dropbox\Accounts Miqlat\Imported\" 'Destination to move completed file too
MyExt = "*.xlsx"
MyFile = Dir(MyPath & MyExt)
NewFile = "ERROR - " & MyFile


'Open Source File if like Cash Management
Do Until MyFile = "" '**** Need Loop See Module 4****
    If MyFile Like "*ERROR*" Then
        GoTo NextFile
    End If
    If MyFile Like "*Cash Management*" Then '**** Need an END IF - See Module4 ****
        Set wb = Workbooks.Open(Filename:=MyPath & MyFile)
            With ActiveSheet
'......


do lots of stuff with the tables in the file
'then do this... Your New Code...




Dim strCheck As String
        Dim i As Long
        Dim strTemp As String
        strTemp = MyFile
        strCheck = Dir(DestPath & MyFile)
        Do Until strCheck = ""
            i = i + 1
            strTemp = Replace(MyFile, Mid(MyExt, 2), "(" & i & ")" & Mid(MyExt, 2))
            strCheck = Dir(DestPath & strTemp)
        Loop
        FileCopy MyPath & MyFile, DestPath & strTemp 'copy file to new location
        Kill MyPath & MyFile 'code to delete origianl MyFile


Application.DisplayAlerts = True
    End If
NextFile:
MyFile MyFile = Dir
Loop


MsgBox "All Eligable files have been imported", vbInformation, "Import Complete"


End Select
End Sub

Display More

mar050703 · Oct 28th 2017

Sorry, I should say the error I get is a Run-time Error 5 Invalid procedure or call argument.

mar050703 · Oct 28th 2017

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

Code

Dir(DestPath & StrTemp)

is resetting the original Dir.

It appears there is a workround if I use

Code

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

Code

fso.movefile(myfile, destpath)

if so are they the correct parameters?

**Kenneth Hobson** · Oct 28th 2017

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.

mar050703 · Oct 28th 2017

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

Code

Dim MyPath As String
Dim DestPath As String
Dim MyFile As String
Dim MyExt As String
Dim wb As Workbook
Dim NewFile As String




MyPath = "C:\Users\Owner\Dropbox\Accounts Miqlat\"
DestPath = "C:\Users\Owner\Dropbox\Accounts Miqlat\Imported\" 'Destination to move completed file too
MyExt = "*.xlsx"
MyFile = Dir(MyPath & MyExt)
NewFile = "ERROR - " & MyFile


'Open Source File if like Cash Management
Do Until MyFile = "" '**** Need Loop See Module 4****
    If MyFile Like "*ERROR*" Then
        GoTo NextFile
    End If
    If MyFile Like "*Cash Management*" Then '**** Need an END IF - See Module4 ****
        Set wb = Workbooks.Open(Filename:=MyPath & MyFile)
            With ActiveSheet
'......

Display More

then

Code

FileCopy MyPath & MyFile, DestPath & MyFile 'copy file to new location
        Kill MyPath & MyFile 'code to delete origianl MyFile


Application.DisplayAlerts = True
    End If
NextFile:
MyFile = Dir
Loop

but with the renaming I used:

Code

Dim strCheck As String
        Dim i As Long
        Dim strTemp As String
        strTemp = MyFile
        strCheck = Dir(DestPath & MyFile)
        Do Until strCheck = ""
            i = i + 1
            strTemp = Replace(MyFile, Mid(MyExt, 2), "(" & i & ")" & Mid(MyExt, 2))
            strCheck = Dir(DestPath & strTemp)
        Loop
        FileCopy MyPath & MyFile, DestPath & strTemp 'copy file to new location
        Kill MyPath & MyFile 'code to delete origianl MyFile


Application.DisplayAlerts = True
    End If
NextFile:
MyFile = Dir
Loop

Display More

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.

FileCopy File Exists

Participate now!

Share