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

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

"Rookie_User" wrote:

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

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" wrote:

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

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" wrote:

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" wrote:

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

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

"Rookie_User" wrote:

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" wrote:

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" wrote:

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

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.

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

On Monday, February 1, 2010 at 10:46:02 AM UTC-6, Rookie_User wrote:
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

On 29/08/17 14:33, wrote:
On Monday, February 1, 2010 at 10:46:02 AM UTC-6, Rookie_User wrote:
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?

Why dig up an answered post?

Before anyone wastes time on this, read the thread.

https://groups.google.com/forum/#!to...ng/935qBX3KYyg


--
Adrian C