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

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

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

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

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

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

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

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

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

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


--

Dave Peterson

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

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

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

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

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


--

Dave Peterson

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

On Error resume next
mkdir ....
on error goto 0



Maperalia wrote:

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

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

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

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

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


--

Dave Peterson


--

Dave Peterson