Warning Message to Prevent Overwritting

[maperalia]

I have a macro which does the following:
1.- Save a file at specific path. The filename is taken from a specific cells
2.- Create a database. Find the last empty row then write the new filename.

However, I have noticed that I made a mistake and typed the same data at the
specific cells to create the filename and click the macro to save it. This
filename went at the last empty row of the database (as I expected it) but
overwrote the last filename. Therefore, I have now two filenames with the
same description in my database saved it at different time and overwritten
with the last information.

Is there is a way to write an statement before save the file to warning that
this filename already exist and ask me if I want to overwrite it?.

For your information the statement I use to save the file is the following:

‘&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Dim Boring As String
Dim WO As String
Dim depth As String

WO = Worksheets("DEFAULTS").Range("C3")
Boring = Worksheets("DEFAULTS").Range("C5")
depth = Worksheets("DEFAULTS").Range("C6")
ClienName = Worksheets("DEFAULTS").Range("C8")

Progname = "S:\GEOTEST\shears\" & WO & "\" & Boring & " " & "@" & " " &
depth & ".xls"

‘&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&


Thanks in advance.
Maperalia

 

[Dave Peterson]

after you determine the progname:

dim resp as long

.....lots of code

resp = vbyes
if dir(progname) <> "" then
resp = Msgbox(Prompt:="Overwrite existing file?", buttons:=vbyesno)
end if
if resp = vbyes then
'save the file
else
'warning and get out???
end if

(Not too much error checking in this. So watch out.)

 

[maperalia]

Dave;
Thanks for your quick response.
I put the statement in my program eventhough I got the window message if I
want to overwrite it Excel crashs it after is doen.
Do you know waht could happened.

Thanks.
Maperalia

 

[Dave Peterson]

Maybe you put the code in the wrong spot--hard to tell without seeing the rest
of the code.

Or maybe you still got the overwrite message and decided to cancel so the
..SaveAs failed.

dim resp as long

.....lots of code

resp = vbyes
if dir(progname) <> "" then
resp = Msgbox(Prompt:="Overwrite existing file?", buttons:=vbyesno)
end if
if resp = vbyes then
'stop the warning message
application.displayalerts = false
'now your code to save the file
application.displayalerts = true
else
'warning and get out???
end if

 

[Maperalia]

Dave;

I am using your code that you previously sent me (see below). However, when
I run the macro and the directory does not exist the macro create it, In
another hands, when the directory already exist and I run the macro I got the
following error message:

Run-time error '75':
Path/File access error

When I click debug it is highligthing at:
MkDir "C:\Paper\" & Format(Sheets("Test").Range("D13").Value)

Could you please tell me what I am doing wrong?
I will really appreciate it.

Thanks in advance.
Maperalia

Sub Message_OF_Existen_Directory()
Dim DirName As String
Dim resp As Long

DirName = "C:\Paper\" & Format(Sheets("Test").Range("D13").Value)
resp = vbYes
If Dir(DirName) <> "" Then
resp = MsgBox(Prompt:="Overwrite existing folder?", Buttons:=vbYesNo)
End If
If resp = vbYes Then
'stop the warning message
Application.DisplayAlerts = False
'now your code to save the file
MkDir "C:\Paper\" & Format(Sheets("Test").Range("D13").Value)
Application.DisplayAlerts = True
Else
'warning and get out???
End If

End Sub

 

[Dave Peterson]

If the directory already exists, you can just ignore the error:

On Error resume next
mkdir ....

Dave;

I am using your code that you previously sent me (see below). However, when
I run the macro and the directory does not exist the macro create it, In
another hands, when the directory already exist and I run the macro I got the
following error message:

Run-time error '75':
Path/File access error

When I click debug it is highligthing at:
MkDir "C:\Paper\" & Format(Sheets("Test").Range("D13").Value)

Could you please tell me what I am doing wrong?
I will really appreciate it.

Thanks in advance.
Maperalia

Sub Message_OF_Existen_Directory()
Dim DirName As String
Dim resp As Long

DirName = "C:\Paper\" & Format(Sheets("Test").Range("D13").Value)
resp = vbYes
If Dir(DirName) <> "" Then
resp = MsgBox(Prompt:="Overwrite existing folder?", Buttons:=vbYesNo)
End If
If resp = vbYes Then
'stop the warning message
Application.DisplayAlerts = False
'now your code to save the file
MkDir "C:\Paper\" & Format(Sheets("Test").Range("D13").Value)
Application.DisplayAlerts = True
Else
'warning and get out???
End If

End Sub

 

[Maperalia]

Dave;
Thanks for your quick response.

I ran the macro with the statement you gave me. However, If the directory
already exist. It has been re-writting it without showing the warning window
message "Overwrite existing folder?"

Could you please check my code (see below) and let me know what is my mistake.

Thanks.
Maperalia

Sub Message_OF_Existen_Directory()
Dim DirName As String
Dim resp As Long

DirName = "C:\Paper\" & Format(Sheets("Test").Range("D13").Value)
resp = vbYes
If Dir(DirName) <> "" Then
resp = MsgBox(Prompt:="Overwrite existing folder?", Buttons:=vbYesNo)
End If
If resp = vbYes Then
Application.DisplayAlerts = False
On Error Resume Next
MkDir "C:\Paper\" & Format(Sheets("Test").Range("D13").Value) On
Error GoTo 0
Application.DisplayAlerts = True
Else
End If
'If the directory already exists, you can just ignore the error:
End Sub

 

[Dave Peterson]

I'm confused about what you're trying to do.

The code I gave you initially was to overwrite an existing file.

So is the filename contained in D13 of the Test worksheet?
What is in that cell?
What does format("Sheets("Test").range("d13").value) return?

Or are you really trying to create a folder/subdirectory????

Dave;
Thanks for your quick response.

I ran the macro with the statement you gave me. However, If the directory
already exist. It has been re-writting it without showing the warning window
message "Overwrite existing folder?"

Could you please check my code (see below) and let me know what is my mistake.

Thanks.
Maperalia

Sub Message_OF_Existen_Directory()
Dim DirName As String
Dim resp As Long

DirName = "C:\Paper\" & Format(Sheets("Test").Range("D13").Value)
resp = vbYes
If Dir(DirName) <> "" Then
resp = MsgBox(Prompt:="Overwrite existing folder?", Buttons:=vbYesNo)
End If
If resp = vbYes Then
Application.DisplayAlerts = False
On Error Resume Next
MkDir "C:\Paper\" & Format(Sheets("Test").Range("D13").Value) On
Error GoTo 0
Application.DisplayAlerts = True
Else
End If
'If the directory already exists, you can just ignore the error:
End Sub

 

[Maperalia]

Dave;

I so sorry to confuse you. This is what I want to do in the macro:
1.- Look if the directory already exist.
2.- Has a window message to ask me " The directory Already Exist. Do you
want to Rewritte it?". Window message will have the "YES' and "NO". "Yes" to
rewritte it and "NO" will give me the option to retype the cell D13.

Kind regards.
Maperalia

 

[Dave Peterson]

Directories are folders. Files are saved inside folders.

So I'm still confused.

And you didn't answer this portion:

Dave;

I so sorry to confuse you. This is what I want to do in the macro:
1.- Look if the directory already exist.
2.- Has a window message to ask me " The directory Already Exist. Do you
want to Rewritte it?". Window message will have the "YES' and "NO". "Yes" to
rewritte it and "NO" will give me the option to retype the cell D13.

Kind regards.
Maperalia

 

[Maperalia]

Dave;
Basically;
The macro just create directory. It does not create files. The description
in the sheet "test" cell "D13" will be directory name.
I hope this answer your question.

Kind regards.
Maperalia

 

[Dave Peterson]

Can you explain how this fails.

On Error resume next
mkdir "C:\Paper\" & Sheets("Test").Range("D13").Value
on error goto 0

If c:\paper doesn't exist:

On Error resume next
mkdir "C:\paper"
mkdir "C:\Paper\" & Sheets("Test").Range("D13").Value

Dave;
Basically;
The macro just create directory. It does not create files. The description
in the sheet "test" cell "D13" will be directory name.
I hope this answer your question.

Kind regards.
Maperalia

 

[Maperalia]

Dave;
It is not falling!!. It creates the directory without problem. However,, my
concern is that if two months later I type the same description on the cell
"D13" and do not want to rewritte the directory. I would like to have a
window message to warning me that this directory already exist.

Kind regards.
Maperalia

 

[Dave Peterson]

The MkDir command won't overwrite the folder. It just tries to create a folder
by that name.

If the folder already exists, then MkDir will fail to create the folder. It
won't remove any files from that folder. It won't delete the folder itself.

By surrounding the MkDir with the "on error..." stuff, your code will just
ignore any error if that folder already exists.

(Working with folders is different than working with files.)

Dave;
It is not falling!!. It creates the directory without problem. However,, my
concern is that if two months later I type the same description on the cell
"D13" and do not want to rewritte the directory. I would like to have a
window message to warning me that this directory already exist.

Kind regards.
Maperalia

 

[Maperalia]

Dave;

Thanks very much for your explanation. It is my understanding that I can not
have a window message to warn me that the directory already exist.
Eventhough, it is not overwritten the directory. The problem is that if I do
not have the warning message it will over create again. Then the files under
this directory will we overwritten because the final macro I have basically
creates a directory and filesnames with the description typed in the cell
"D13"

Thanks for your support.
Kind regards.
Maperalia

 

[Dave Peterson]

Nope.

The mkdir command either creates the folder or fails to create the folder. It
will not create it again. It will not create a second copy of the folder.

On the other hand, when you're saving a file, you can overwrite those files.
But this isn't a problem with the mkdir command.

You can check with something like:

Dim myStr as string
mystr = ""
on error resume next
mystr = dir("C:\paper\test\myfilenamehere.xls")
on error goto 0

if mystr = "" then
'then "C:\paper\test\myfilenamehere.xls" does not exist
else
'it does exist.
end if

So does D13 contain the name of the file--or does it contain the name of the
folder?

There's a big difference.

 

[Maperalia]

Dave;
I really appreciate your supporting in this matter.

Regarding your question; the cell "D13" contains the name of the folder and
the filename. For example, if the description on the cell "D13" is WO9999.
The macro does the following:
1.- Create a directory named WO9999 and located it under one specific path.
2.- Create a filename WO9999.xls and located under the directory WO9999
3.- Create a filename WO9999.dwg and located under the directory WO9999

Therefore, the directory WO9999 will have all the package of the filesname
WO9999. This is the reason that in the future if I will be given the same
description (WO9999). The macro will overwritte these files and is exactly
what I want to avoid.
I would like to have the warning window message to telll me that this
description WO9999 has already been given. So I can tell this person to use
another description instead.

Kind regards.
Maperalia

 

[Dave Peterson]

Dim TestStr as string
teststr = ""
on error resume next
teststr = dir(yourlongpathexpression & "\nul")
on error goto 0

if teststr = "" then
'folder doesn't exist
else
'folder exists
end if

 

[Maperalia]

Dave;
Thanks for the code. It is workinf find. However, How can I get the window
message to warn me that the directory already exist?
Kind regards.
Maperalia

 

[Dave Peterson]

....
else
msgbox "That folder exists"
end if

Dave;
Thanks for the code. It is workinf find. However, How can I get the window
message to warn me that the directory already exist?
Kind regards.
Maperalia