Error 58, file already exists - why?

[Rookie_User]

I am trying to move a file from one folder to another - or at least copy and
delete the old file. I get an error 58 file already exists, and don't know
why?



Public Sub MoveFiles(ByVal Source As String, ByVal Destination As String)
Dim oFSO As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
With oFSO
If .FileExists(Source) Then
..MoveFile Source, Destination
Else
MsgBox Source & " Doesn't Exist", vbExclamation
End If
End With
Set oFSO = Nothing
End Sub

Sub RunThroughList()
Dim I As Long
Dim Destination As String
Dim Lastrow As Long

Lastrow = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row

For I = 1 To Lastrow
MoveFiles Range("A" & I).Value, "C:\Output"

Next
End Sub

 

[Matthew Herbert]

Rookie_User,

Are your names fully qualified? For example, you need to use
C:\MoveFrom\test.txt rather than test.txt.

Have you looked over the help documentation for MoveFile Method (i.e. you
know how source should be set up and you know the implications of leaving off
"\" for destination)? Also, you may want to consider the Name Statement (see
the VBE Help files for this).

Without me creating any code to mirror what you are doing, I would start by
examining the help documentation and ensure that your arguments are correct.

Best,

Matthew Herbert

 

[Matthew Herbert]

Rookie_User,

I left out one of my questions in the original post. Since you are not
testing if the destination already has the source file, do any of source
files already exist in destination?

Best,

Matthew Herbert

 

[Rookie_User]

Thank you for getting back to me, there are no existing files in the
destination folder - confirmed. I tried to follow the code but when using
ofs - it's an area I am not familiar with. The code above just fails
(runtime failure error 58) and says file already exists.

In column A I have the full path c:\input\text.txt
and the output folder is c:\output

The watch statements appear to have the correct values in them.

Sorry for the late reply - even when I have the box "notify me of replies"
marked, I do not get notified. I have checked my junk mail, etc.. and
nothing so I am unsure why. But I will check more frequently. Thanks again
for your help and effort.

 

[Matthew Herbert]

Rookie_User,

I did some mock tests. Here are my general observations (and pay close
attention to the subtle differences in "Destination"):

Scenario 1
- Source - C:\Input\test.txt
- Destination - C:\Output\
- Destination file - test.txt
Result: Run-time error '58' File already exists

Scenario 2
- Source - C:\Input\test.txt
- Destination - C:\Output
- Destination file - N/A
Result: Run-time error '58' File already exists

Scenario 3
- Source - C:\Input\test.txt
- Destination - C:\Output\
- Destination file - N/A
Result: File Moved

So, what's the subtle difference? As mentioned previously, the MoveFile
Method helps to shed some light on the issue. The Help file states that "If
.... destination ends with a path separator (\), it is assumed that
destination specifies an existing folder in which to move the matching files.
Otherwise, destination is assumed to be the name of a destination file to
create..." So, Scenario 2 is your current scenario for the code in your post
(i.e. NO "\" on destination, or "C:\Output"). As the help states, if the
separator is absent, then the system tries to create the destination. If
your destination folder ALREADY exists, and you do NOT use the "\" on the
destination folder path, then the system tries to create an already existing
folder. Thus, the error occurs because you are trying to recreate a folder
that already exists.

What should be done? You can insert the "\" on the end of your destination
folder and/or test for the folder prior to .MoveFile. (Personally, I think
you should test for the existence of the destination folder prior to
..MoveFile (via .FolderExists) AND test for the existence of the source file
in destination prior to .MoveFile (via .FileExists). Testing these two
scenarios will at least this care of two of the situations that will
inevitably occur. I put some code below for you to reference).

Best,

Matt

Public Sub MoveFiles(ByVal Source As String, ByVal Destination As String)
Dim oFSO As Object
Dim strDestPath As String
Set oFSO = CreateObject("Scripting.FileSystemObject")
With oFSO

'test if the destination folder exists
If .FolderExists(Destination) Then

'test if the source file exists in destination
strDestPath = .GetFolder(Destination) & "\" & _
.GetFileName(Source)

If Not .FileExists(strDestPath) Then

'test if the source file exists
If .FileExists(Source) Then

'move the file
.MoveFile Source, Destination
Else
MsgBox Source & " Doesn't Exist", vbExclamation
End If
End If
End If
End With
Set oFSO = Nothing
End Sub

Sub RunThroughList()
Dim I As Long
Dim Destination As String
Dim Lastrow As Long

Lastrow = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row

For I = 1 To Lastrow
MoveFiles Range("A" & I).Value, "C:\Output\"
Next I

End Sub

 

[Bailey]

WOW, the dialogue really helps me understand and walk through exactly what
your meaning AND you were exactly correct. Thank you for your time,
education and your efforts.

 

[Bailey]

Mathew, in your above code in the public sub section - how might I color code
that cell or put a value of missing in the next column - this is so I can
identify all files that are missing or vice versa identify those that are
successful.

I tried passing the value of I into the public sub as a long, but then I did
this
..cells ("A",I).interiorcolor.index=6

but I get a type mismatch?

any suggestiosn

 

[Matthew Herbert]

Bailey,

Highlighting a cell uses the Interior property of a range, so the syntax
could look like the following:

Range("A1").Interior.ColorIndex = 6

In your specific situation, I would probably change your Public Sub to a
Function with a Boolean data type. This way, you can return True for a
successful file move or False for an unsuccessful file move. If False, then
you can color the cell via the RunThroughList procedure. I've provided some
illustrative code below (and removed the MsgBox because that will likely get
rather annoying, if that situation every occurs given the pre-tests for file
and folder). When using Boolean data types, the variable is initialized as
False, so unless you explicitly change it to True, the value will always be
False. I did NOT test the code, but I did change the name of the function
from MoveFiles to IsFileMoved.

I want to point out two subtle notes.
(1) Be careful with "qualifying your ranges." Rather than re-explaining a
concept, see my post (i.e. "Sub QualifyRanges..." procedure and explanation)
in the following thread (the 4th or 5th post):
http://groups.google.com/group/micr...ramming/browse_thread/thread/6f5137ca83744b60

(2) You should probably clear the highlighting prior to running
"RunThroughList" because you may end up with incorrect results if you run
this procedure more than once. (For example, an error on the first run of
"RunThroughList" might be highlighted, but that error might not occur on the
second run (i.e. should not be highlighted but since it was highlighted on
the first run the cell shows an incorrect highlight)). An alternative would
be something like If True Then clear the highlighting (i.e. I think you can
use .Interior.ColorIndex = xlNone) Else highlight as desired.

Best,

Matthew Herbert

Public Function IsFileMoved(ByVal Source As String, _
ByVal Destination As String) As Boolean
Dim oFSO As Object
Dim strDestPath As String
Set oFSO = CreateObject("Scripting.FileSystemObject")
With oFSO

'test if the destination folder exists
If .FolderExists(Destination) Then

'test if the source file exists in destination
strDestPath = .GetFolder(Destination) & "\" & _
.GetFileName(Source)

If Not .FileExists(strDestPath) Then

'test if the source file exists
If .FileExists(Source) Then

'move the file
.MoveFile Source, Destination
IsFileMoved = True
Else
IsFileMoved = False
End If
End If
End If
End With
Set oFSO = Nothing
End Function

Sub RunThroughList()
Dim I As Long
Dim Destination As String
Dim Lastrow As Long
Dim blnMoved As Boolean

Lastrow = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row

For I = 1 To Lastrow

'here is how to do it in a single statement:
'If Not IsFileMoved(Range("A" & I).Value, "C:\Output\") Then
' color the cell
'End If

'here is how to do it with a variable
blnMoved = IsFileMoved(Range("A" & I).Value, "C:\Output\")
If Not blnMoved Then
Range("A" & I).Interior.ColorIndex = 6
End If
Next I

End Sub