Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
More SaveAs trouble
I jumped the gun thinking I had it figured out...
I'm trying to bulletproof a spreadsheet template against being overwritten by coworkers who must use it. I have set it up to force them to save it as a normal Excel spreadsheet under a different name any time the template is activated. All works fine unless I choose NOT to overwrite an existing file. To be clear, the saveas dialog appears and I choose a file name that already exists. I am then asked if I wish to overwrite the file. If I say 'Yes' everything goes fine. However, choosing 'No' or 'Cancel' results in an error. Here's a simplified example of what I have so far: Sub SaveAsX() NameFile: On Error GoTo SaveAsErrors FileSaveName = "Test" While FileSaveName = "Test" FileSaveName = Application.GetSaveAsFilename(InitialFileName:= _ "C:\temp\Test", fileFilter:="Microsoft Excel Workbook (*.xls), *.xls") Wend ThisWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=-4143 Exit Sub SaveAsErrors: If Err.Number = 1004 Then GoTo NameFile End Sub If I choose 'No' or 'Cancel' the first time the error is handled fine, the SaveAs dialog box appears again. However, if I choose either button again the second time it creates the same error but it is not caught by the error trap. Why is this. Help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
More SaveAs trouble
GOT IT!! I really hate feeling like I have hit a brick wall, asking for
help, and then finding the answer. Seems to inspire me, though. "Shawn Shuler" wrote in message ... I jumped the gun thinking I had it figured out... I'm trying to bulletproof a spreadsheet template against being overwritten by coworkers who must use it. I have set it up to force them to save it as a normal Excel spreadsheet under a different name any time the template is activated. All works fine unless I choose NOT to overwrite an existing file. To be clear, the saveas dialog appears and I choose a file name that already exists. I am then asked if I wish to overwrite the file. If I say 'Yes' everything goes fine. However, choosing 'No' or 'Cancel' results in an error. Here's a simplified example of what I have so far: Sub SaveAsX() NameFile: On Error GoTo SaveAsErrors FileSaveName = "Test" While FileSaveName = "Test" FileSaveName = Application.GetSaveAsFilename(InitialFileName:= _ "C:\temp\Test", fileFilter:="Microsoft Excel Workbook (*.xls), *.xls") Wend ThisWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=-4143 Exit Sub SaveAsErrors: If Err.Number = 1004 Then GoTo NameFile End Sub If I choose 'No' or 'Cancel' the first time the error is handled fine, the SaveAs dialog box appears again. However, if I choose either button again the second time it creates the same error but it is not caught by the error trap. Why is this. Help! |