Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
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???? Maperalia wrote: 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" wrote: 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 -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
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" wrote: 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???? Maperalia wrote: 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" wrote: 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 -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
Directories are folders. Files are saved inside folders.
So I'm still confused. And you didn't answer this portion: 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? Maperalia wrote: 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" wrote: 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???? Maperalia wrote: 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" wrote: 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 -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
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" wrote: Directories are folders. Files are saved inside folders. So I'm still confused. And you didn't answer this portion: 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? Maperalia wrote: 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" wrote: 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???? Maperalia wrote: 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" wrote: 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 -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
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 on error goto 0 Maperalia wrote: 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" wrote: Directories are folders. Files are saved inside folders. So I'm still confused. And you didn't answer this portion: 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? Maperalia wrote: 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" wrote: 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???? Maperalia wrote: 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" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
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" wrote: 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 on error goto 0 Maperalia wrote: 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" wrote: Directories are folders. Files are saved inside folders. So I'm still confused. And you didn't answer this portion: 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? Maperalia wrote: 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" wrote: 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???? Maperalia wrote: 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" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
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.) Maperalia wrote: 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" wrote: 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 on error goto 0 Maperalia wrote: 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" wrote: Directories are folders. Files are saved inside folders. So I'm still confused. And you didn't answer this portion: 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? Maperalia wrote: 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" wrote: 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???? Maperalia wrote: 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" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
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" wrote: 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.) Maperalia wrote: 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" wrote: 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 on error goto 0 Maperalia wrote: 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" wrote: Directories are folders. Files are saved inside folders. So I'm still confused. And you didn't answer this portion: 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? Maperalia wrote: 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" wrote: 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???? Maperalia wrote: 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" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
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 wrote: 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" wrote: 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.) Maperalia wrote: 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" wrote: 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 on error goto 0 Maperalia wrote: 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" wrote: Directories are folders. Files are saved inside folders. So I'm still confused. And you didn't answer this portion: 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? Maperalia wrote: 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" wrote: 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???? Maperalia wrote: 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" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- -- Dave Peterson |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
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" wrote: 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 wrote: 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" wrote: 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.) Maperalia wrote: 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" wrote: 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 on error goto 0 Maperalia wrote: 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" wrote: Directories are folders. Files are saved inside folders. So I'm still confused. And you didn't answer this portion: 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? Maperalia wrote: 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" wrote: 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???? Maperalia wrote: 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" wrote: 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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
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 wrote: 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" wrote: 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 wrote: 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" wrote: 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.) Maperalia wrote: 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" wrote: 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 on error goto 0 Maperalia wrote: 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" wrote: Directories are folders. Files are saved inside folders. So I'm still confused. And you didn't answer this portion: 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? Maperalia wrote: 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" wrote: 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???? Maperalia wrote: 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" wrote: 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 -- Dave Peterson |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
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" wrote: 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 wrote: 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" wrote: 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 wrote: 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" wrote: 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.) Maperalia wrote: 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" wrote: 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 on error goto 0 Maperalia wrote: 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" wrote: Directories are folders. Files are saved inside folders. So I'm still confused. And you didn't answer this portion: 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? Maperalia wrote: 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" wrote: 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???? Maperalia wrote: 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" wrote: 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: |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
....
else msgbox "That folder exists" end if Maperalia wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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" wrote: 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.) Maperalia wrote: 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" wrote: 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 on error goto 0 Maperalia wrote: 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" wrote: Directories are folders. Files are saved inside folders. So I'm still confused. And you didn't answer this portion: 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? Maperalia wrote: 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" wrote: 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???? Maperalia wrote: 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" wrote: 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: -- Dave Peterson |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
Dave;
Thanks very much. It is working PERFECTLY!!!!. I really appreciate your helping me with this matter. Kind regards. Maperalia "Dave Peterson" wrote: .... else msgbox "That folder exists" end if Maperalia wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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" wrote: 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.) Maperalia wrote: 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" wrote: 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 on error goto 0 Maperalia wrote: 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" wrote: Directories are folders. Files are saved inside folders. So I'm still confused. And you didn't answer this portion: 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? Maperalia wrote: 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" wrote: 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???? Maperalia wrote: 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" wrote: 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??? |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Message to Prevent Overwritting
Glad you got it working the way you wanted.
Maperalia wrote: Dave; Thanks very much. It is working PERFECTLY!!!!. I really appreciate your helping me with this matter. Kind regards. Maperalia "Dave Peterson" wrote: .... else msgbox "That folder exists" end if Maperalia wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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" wrote: 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.) Maperalia wrote: 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" wrote: 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 on error goto 0 Maperalia wrote: 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" wrote: Directories are folders. Files are saved inside folders. So I'm still confused. And you didn't answer this portion: 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? Maperalia wrote: 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" wrote: 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???? Maperalia wrote: 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" wrote: 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??? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Warning Message | Excel Worksheet Functions | |||
warning message | Excel Discussion (Misc queries) | |||
How to prevent warning messages on protected work sheet. | Excel Worksheet Functions | |||
prevent warning message from appearing | Excel Programming | |||
warning message | Excel Programming |