|
Prevent overwriting a file
The code below is triggered by a command1 button on on UserForm1. It saves
the document with a name derived by combining data in certain cells. My problem is if that document name already exists. I do not want the user to have the option of replacing the original file or renaming the current file. I want a MsgBox which tells them that the file already exists. Once the MsgBox is dismissed I want UserForm1 to reappear so they can select a new date. Private Sub CommandButton1_Click() Dim myRange As Range Dim myDate As Range Set myRange = Worksheets("Relief Board").Range("C3") Set myDate = Worksheets("Relief Board").Range("C4") Protection.unprotect_all_sheets myRange.Value = "" myDate.Value = Calendar1.Value Protection.protect_all_sheets ActiveWorkbook.SaveAs Filename:="P:\AA Exception\ " & Worksheets("Relief Board").[B3].Value & ", " & Worksheets("Relief Board").[D3].Value & "_Exception Sheet", _ FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Unload StartNewDay End Sub |
Prevent overwriting a file
Patrick C. Simonds;205378 Wrote: The code below is triggered by a command1 button on on UserForm1. It saves the document with a name derived by combining data in certain cells. My problem is if that document name already exists. I do not want the user to have the option of replacing the original file or renaming the current file. I want a MsgBox which tells them that the file already exists. Once the MsgBox is dismissed I want UserForm1 to reappear so they can select a new date. Code: -------------------- Private Sub CommandButton1_Click() Dim myRange As Range Dim myDate As Range Set myRange = Worksheets("Relief Board").Range("C3") Set myDate = Worksheets("Relief Board").Range("C4") Protection.unprotect_all_sheets myRange.Value = "" myDate.Value = Calendar1.Value Protection.protect_all_sheets ActiveWorkbook.SaveAs Filename:="P:\AA Exception\ " & Worksheets("Relief Board").[B3].Value & ", " & Worksheets("Relief Board").[D3].Value & "_Exception Sheet", _ FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Unload StartNewDay End Sub -------------------- Hello Patrick, You can use the Dir statement to test if a file exists. You can even use wild card characters to match similar names. Code: -------------------- FileName = Dir("P:\AA Exception\MyFile.xls") 'If the file doesn't exist, FileName will be an empty string "". If FileName < "" Then MsgBox "File Exists. Please Use a Different Name." UserForm1.Show End If -------------------- -- Leith Ross Sincerely, Leith Ross 'The Code Cage' (http://www.thecodecage.com/) ------------------------------------------------------------------------ Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=56428 |
Prevent overwriting a file
I have to admit, I do not even know where to begin to integrate this into my
current code. "Leith Ross" wrote in message ... Patrick C. Simonds;205378 Wrote: The code below is triggered by a command1 button on on UserForm1. It saves the document with a name derived by combining data in certain cells. My problem is if that document name already exists. I do not want the user to have the option of replacing the original file or renaming the current file. I want a MsgBox which tells them that the file already exists. Once the MsgBox is dismissed I want UserForm1 to reappear so they can select a new date. Code: -------------------- Private Sub CommandButton1_Click() Dim myRange As Range Dim myDate As Range Set myRange = Worksheets("Relief Board").Range("C3") Set myDate = Worksheets("Relief Board").Range("C4") Protection.unprotect_all_sheets myRange.Value = "" myDate.Value = Calendar1.Value Protection.protect_all_sheets ActiveWorkbook.SaveAs Filename:="P:\AA Exception\ " & Worksheets("Relief Board").[B3].Value & ", " & Worksheets("Relief Board").[D3].Value & "_Exception Sheet", _ FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Unload StartNewDay End Sub -------------------- Hello Patrick, You can use the Dir statement to test if a file exists. You can even use wild card characters to match similar names. Code: -------------------- FileName = Dir("P:\AA Exception\MyFile.xls") 'If the file doesn't exist, FileName will be an empty string "". If FileName < "" Then MsgBox "File Exists. Please Use a Different Name." UserForm1.Show End If -------------------- -- Leith Ross Sincerely, Leith Ross 'The Code Cage' (http://www.thecodecage.com/) ------------------------------------------------------------------------ Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=56428 |
Prevent overwriting a file
Patrick C. Simonds;205537 Wrote: I have to admit, I do not even know where to begin to integrate this into my current code. "Leith Ross" wrote in message ... Patrick C. Simonds;205378 Wrote: The code below is triggered by a command1 button on on UserForm1. It saves the document with a name derived by combining data in certain cells. My problem is if that document name already exists. I do not want the user to have the option of replacing the original file or renaming the current file. I want a MsgBox which tells them that the file already exists. Once the MsgBox is dismissed I want UserForm1 to reappear so they can select a new date. Code: -------------------- Private Sub CommandButton1_Click() Dim myRange As Range Dim myDate As Range Set myRange = Worksheets("Relief Board").Range("C3") Set myDate = Worksheets("Relief Board").Range("C4") Protection.unprotect_all_sheets myRange.Value = "" myDate.Value = Calendar1.Value Protection.protect_all_sheets ActiveWorkbook.SaveAs Filename:="P:AA Exception " & Worksheets("Relief Board").[B3].Value & ", " & Worksheets("Relief Board").[D3].Value & "_Exception Sheet", _ FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Unload StartNewDay End Sub -------------------- Hello Patrick, You can use the Dir statement to test if a file exists. You can even use wild card characters to match similar names. Code: -------------------- FileName = Dir("P:AA ExceptionMyFile.xls") 'If the file doesn't exist, FileName will be an empty string "". If FileName < "" Then MsgBox "File Exists. Please Use a Different Name." UserForm1.Show End If -------------------- -- Leith Ross Sincerely, Leith Ross 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com/)) ------------------------------------------------------------------------ Leith Ross's Profile: 'The Code Cage Forums - View Profile: Leith Ross' (http://www.thecodecage.com/forumz/me...eith-ross.html) View this thread: 'Prevent overwriting a file - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=56428) Hello Patrick, I added the code for you. Try it out and let me know if there are problems. Code: -------------------- Private Sub CommandButton1_Click() Dim FileName As String Dim myRange As Range Dim myDate As Range Set myRange = Worksheets("Relief Board").Range("C3") Set myDate = Worksheets("Relief Board").Range("C4") Protection.unprotect_all_sheets myRange.Value = "" myDate.Value = Calendar1.Value Protection.protect_all_sheets CHeckFileName: FileName = "P:\AA Exception\ " _ & Worksheets("ReliefBoard").[B3].Value _ & ", " & Worksheets("Relief Board").[D3].Value _ & "_Exception Sheet" 'If the file doesn't exist, FileName will be an empty string "". If Dir(FileName) < "" Then MsgBox "File Exists. Please Use a Different Name." UserForm1.Show GoTo CheckFileName End If ActiveWorkbook.SaveAs FileName:=FileName, _ FileFormat:=xlOpenXMLWorkbookMacroEnabled, _ Password:="", _ WriteResPassword:="", _ ReadOnlyRecommended:=False, _ CreateBackup:=False Unload StartNewDay End Sub -------------------- -- Leith Ross Sincerely, Leith Ross 'The Code Cage' (http://www.thecodecage.com/) ------------------------------------------------------------------------ Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=56428 |
Prevent overwriting a file
Thank you
I get a Subscript out of range error with the code below highlighted. I thought the problem might be that the type of file was not indicated (in this case xlsm) so I tried adding before the last quote mark but I still got the same error. FileName = "P:\AA Exception\ " _ & Worksheets("ReliefBoard").[B3].Value _ & ", " & Worksheets("Relief Board").[D3].Value _ & "_Exception Sheet" |
Prevent overwriting a file
Patrick C. Simonds;205750 Wrote: Thank you I get a Subscript out of range error with the code below highlighted. I thought the problem might be that the type of file was not indicated (in this case xlsm) so I tried adding before the last quote mark but I still got the same error. FileName = "P:\AA Exception\ " _ & Worksheets("ReliefBoard").[B3].Value _ & ", " & Worksheets("Relief Board").[D3].Value _ & "_Exception Sheet" Hello Patrick, The only subscripts would be cells. Everything else is a fixed string. Do the cells have the correct values? Are all the names of the worksheets spelled correctly? If all that checks out OK, I really don't know what the problem would be. I don't have Excel 2007 so I can't run it even if you posted the workbook |
Prevent overwriting a file
I am sorry to have wasted your time on that last question. I have been so
wrapped up in this project I totally missed that ReliefBoard should have been Relief Board (two words). "Leith Ross" wrote in message ... Patrick C. Simonds;205750 Wrote: Thank you I get a Subscript out of range error with the code below highlighted. I thought the problem might be that the type of file was not indicated (in this case xlsm) so I tried adding before the last quote mark but I still got the same error. FileName = "P:\AA Exception\ " _ & Worksheets("ReliefBoard").[B3].Value _ & ", " & Worksheets("Relief Board").[D3].Value _ & "_Exception Sheet" Hello Patrick, The only subscripts would be cells. Everything else is a fixed string. Do the cells have the correct values? Are all the names of the worksheets spelled correctly? If all that checks out OK, I really don't know what the problem would be. I don't have Excel 2007 so I can't run it even if you posted the workbook . -- Leith Ross Sincerely, Leith Ross 'The Code Cage' (http://www.thecodecage.com/) ------------------------------------------------------------------------ Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=56428 |
Prevent overwriting a file
Patrick C. Simonds;205854 Wrote: I am sorry to have wasted your time on that last question. I have been so wrapped up in this project I totally missed that ReliefBoard should have been Relief Board (two words). "Leith Ross" wrote in message ... Patrick C. Simonds;205750 Wrote: Thank you I get a Subscript out of range error with the code below highlighted. I thought the problem might be that the type of file was not indicated (in this case xlsm) so I tried adding before the last quote mark but I still got the same error. FileName = "P:AA Exception " _ & Worksheets("ReliefBoard").[B3].Value _ & ", " & Worksheets("Relief Board").[D3].Value _ & "_Exception Sheet" Hello Patrick, The only subscripts would be cells. Everything else is a fixed string. Do the cells have the correct values? Are all the names of the worksheets spelled correctly? If all that checks out OK, I really don't know what the problem would be. I don't have Excel 2007 so I can't run it even if you posted the workbook . -- Leith Ross Sincerely, Leith Ross 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com/)) ------------------------------------------------------------------------ Leith Ross's Profile: 'The Code Cage Forums - View Profile: Leith Ross' (http://www.thecodecage.com/forumz/me...eith-ross.html) View this thread: 'Prevent overwriting a file - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=56428) Hello Patrick, I misspell words a lot when coding. My typing skills are my weakness, Did that solve the problem? -- Leith Ross Sincerely, Leith Ross 'The Code Cage' (http://www.thecodecage.com/) ------------------------------------------------------------------------ Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=56428 |
| All times are GMT +1. The time now is 10:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com