move files and prompt user if file already exists in target filepath

[Mike]

I have the following code that moves files between folders.
Unfortunately, already existing files get overwritten if the new
filename is the same. Is there a way to prompt the user in case the
file would already exist and to abort the copy kill operation?

here's my code:

Private Sub Command9_Click()
Dim strNewName As String
Dim strOldName As String
Dim strtargetfilepath As String
strtargetfilepath = DLookup("[Draft to Active]", "filepath")

strOldName = Me.Filename
strNewName = strtargetfilepath & "\" & Me.Name1
FileCopy strOldName, strNewName

Kill strOldName

End Sub

The new name of the file is created by using the targetfilepath and
adding the filename (without the old filepath). But it could be that
this filepath already exists.

Thanks for any help.

 

[Dirk Goldgar]

In

I have the following code that moves files between folders.
Unfortunately, already existing files get overwritten if the new
filename is the same. Is there a way to prompt the user in case the
file would already exist and to abort the copy kill operation?

here's my code:

Private Sub Command9_Click()
Dim strNewName As String
Dim strOldName As String
Dim strtargetfilepath As String
strtargetfilepath = DLookup("[Draft to Active]", "filepath")

strOldName = Me.Filename
strNewName = strtargetfilepath & "\" & Me.Name1
FileCopy strOldName, strNewName

Kill strOldName

End Sub

The new name of the file is created by using the targetfilepath and
adding the filename (without the old filepath). But it could be that
this filepath already exists.

Thanks for any help.

'----- start of code snippet -----
If Len(Dir(strNewName)) > 0 Then

If MsgBox( _
"File '" & strNewName & "' already exists. " & _
"Do you want to overwrite it?", _
vbQuestion+vbYesNo,
"Overwrite Existing File?") _
= vbNo _
Then
Exit Sub
End If

End If
'----- end of code snippet -----

 

[Mike]

In

I have the following code that movesfilesbetween folders.
Unfortunately, already existingfilesget overwritten if the new
filename is the same. Is there a way to prompt the user in case the
file would already exist and to abort the copy kill operation?

here's my code:

Private Sub Command9_Click()
Dim strNewName As String
Dim strOldName As String
Dim strtargetfilepath As String
strtargetfilepath = DLookup("[Draft to Active]", "filepath")

strOldName = Me.Filename
strNewName = strtargetfilepath & "\" & Me.Name1
FileCopy strOldName, strNewName

Kill strOldName

End Sub

The new name of the file is created by using the targetfilepath and
adding the filename (without the old filepath). But it could be that
this filepath already exists.

Thanks for any help.

'----- start of code snippet -----
If Len(Dir(strNewName)) > 0 Then

If MsgBox( _
"File '" & strNewName & "' already exists. " & _
"Do you want to overwrite it?", _
vbQuestion+vbYesNo,
"Overwrite Existing File?") _
= vbNo _
Then
Exit Sub
End If

End If
'----- end of code snippet -----

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

I copied and pasted the code snipped before the filecopy command. The
complete if msgbox statement is displayed in red. Can you help me?
Other than that, it looks like it would be what I've been looking for
(as far as I can judge this with my limited VBA knowledge). Thanks for
your help

 

[Mike]

In

I have the following code that movesfilesbetween folders.
Unfortunately, already existingfilesget overwritten if the new
filename is the same. Is there a way to prompt the user in case the
file would already exist and to abort the copy kill operation?

here's my code:

Private Sub Command9_Click()
Dim strNewName As String
Dim strOldName As String
Dim strtargetfilepath As String
strtargetfilepath = DLookup("[Draft to Active]", "filepath")

strOldName = Me.Filename
strNewName = strtargetfilepath & "\" & Me.Name1
FileCopy strOldName, strNewName

Kill strOldName

End Sub

The new name of the file is created by using the targetfilepath and
adding the filename (without the old filepath). But it could be that
this filepath already exists.

Thanks for any help.

'----- start of code snippet -----
If Len(Dir(strNewName)) > 0 Then

If MsgBox( _
"File '" & strNewName & "' already exists. " & _
"Do you want to overwrite it?", _
vbQuestion+vbYesNo,
"Overwrite Existing File?") _
= vbNo _
Then
Exit Sub
End If

End If
'----- end of code snippet -----

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

I'm trying again to post, but unfortunately it seems that our IT
department has some problems. Whenever I post something today, I'm not
able to review the posts without rebooting my PC. So, just to let you
know:
I've deleted all the if code around the messagebox-part and as I don't
really know how this works:

MsgBox( _
"File '" & strNewName & "' already exists. " & _
"Do you want to overwrite it?", _
vbQuestion+vbYesNo,
"Overwrite Existing File?") _
= vbNo _

I've just replaced it with MsgBox "File already exists", vbOKonly

That worked. Thanks a lot, your answer really got me to where I wanted
to be.

Can you tell me where I find resources to the MsgBox part that you
initally posted so that I can understand the syntax?

Thanks again.

 

[Dirk Goldgar]

In

I copied and pasted the code snipped before the filecopy command. The
complete if msgbox statement is displayed in red. Can you help me?
Other than that, it looks like it would be what I've been looking for
(as far as I can judge this with my limited VBA knowledge). Thanks for
your help

Oops, I left off one line-continuation character. The code should have
been:

If MsgBox( _
"File '" & strNewName & "' already exists. " & _
"Do you want to overwrite it?", _
vbQuestion+vbYesNo, _
"Overwrite Existing File?") _
= vbNo _
Then
Exit Sub
End If

Try that.

 

[Dirk Goldgar]

In

Can you tell me where I find resources to the MsgBox part that you
initally posted so that I can understand the syntax?

The MsgBox function is documented in the online help, but you won't find
it easily unless you open the help from the VB Editor environment. The
easiest way to open the appropriate help topic is to click on the word
"MsgBox" in the code while you're editing it, then press F1.

In this case, the error in what I posted was really not so much to do
with the MsgBox function, as it it was with the fact that I left off a
line-continuation character. So what you'd need help on to figure that
out is really not the function itself, but on general VB statement
syntax. Unfortunately, I just had a quick look through the help file,
and I couldn't find a topic that discussed it. So the help file is
deficient in that regard.

 

[Mike]

In

I have the following code that movesfilesbetween folders.
Unfortunately, already existingfilesget overwritten if the new
filename is the same. Is there a way to prompt the user in case the
file would already exist and to abort the copy kill operation?

here's my code:

Private Sub Command9_Click()
Dim strNewName As String
Dim strOldName As String
Dim strtargetfilepath As String
strtargetfilepath = DLookup("[Draft to Active]", "filepath")

strOldName = Me.Filename
strNewName = strtargetfilepath & "\" & Me.Name1
FileCopy strOldName, strNewName

Kill strOldName

End Sub

The new name of the file is created by using the targetfilepath and
adding the filename (without the old filepath). But it could be that
this filepath already exists.

Thanks for any help.

'----- start of code snippet -----
If Len(Dir(strNewName)) > 0 Then

If MsgBox( _
"File '" & strNewName & "' already exists. " & _
"Do you want to overwrite it?", _
vbQuestion+vbYesNo,
"Overwrite Existing File?") _
= vbNo _
Then
Exit Sub
End If

End If
'----- end of code snippet -----

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Dirk, I'm now at home at my own PC. The tree overview of this group
shows 6 posts, but I only can access the first 2 of them. Do you know
what's going on?

 

[Mike]

In
I have the following code that movesfilesbetween folders.
Unfortunately, already existingfilesget overwritten if the new
filename is the same. Is there a way to prompt the user in case the
file would already exist and to abort the copy kill operation?
here's my code:
Private Sub Command9_Click()
Dim strNewName As String
Dim strOldName As String
Dim strtargetfilepath As String
strtargetfilepath = DLookup("[Draft to Active]", "filepath")
strOldName = Me.Filename
strNewName = strtargetfilepath & "\" & Me.Name1
FileCopy strOldName, strNewName
Kill strOldName
End Sub
The new name of the file is created by using the targetfilepath and
adding the filename (without the old filepath). But it could be that
this filepath already exists.
Thanks for any help.
'----- start of code snippet -----
If Len(Dir(strNewName)) > 0 Then
If MsgBox( _
"File '" & strNewName & "' already exists. " & _
"Do you want to overwrite it?", _
vbQuestion+vbYesNo,
"Overwrite Existing File?") _
= vbNo _
Then
Exit Sub
End If
End If
'----- end of code snippet -----

Dirk, I'm now at home at my own PC. The tree overview of this group
shows 6 posts, but I only can access the first 2 of them. Do you know
what's going on?- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Ok, looks like the system is now posting my stuff. But still can not
access the previous messages.- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Ok, now I could access the older posts. I'll check the help file
tomorrow and also will try your initial code.
Do you have any good resources (books, links) that are worthy to read
for VBA beginners?

At least I've got it working. I really appreciate all your help.

Thanks again

 

[Dirk Goldgar]

In

Ok, now I could access the older posts. I'll check the help file
tomorrow and also will try your initial code.
Do you have any good resources (books, links) that are worthy to read
for VBA beginners?

I don't know what's out there currently. I didn't learn from a book,
but by reading the help files, looking at posted code, and applying
experience gained from other programming languages. You might look at
some of the books and other resources listed by Jeff Conrad, "Access
Junkie":

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#Books

Not all of those are suitable for beginners, so check them out before
you buy.