Home |
Search |
Today's Posts |
#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 |
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 |