Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error trap only works once
Hello :-)
I have some code to save a workbook for the first time using the SaveAs dialog. I need to trap if the same filename is currently being used by aother Excel session -to prevent VBA from getting upset. Essentially the code is: Dim FD As FileDialog ' Variable for dialog object On Error GoTo ErrorHandler 'Create file dialog object Set FD = Application.FileDialog(MsoFileDialogType.msoFileDi alogSaveAs) 'Set the default file name (& path) FD.InitialFileName = Application.DefaultFilePath & "\Planning Spreadsheet" SaveLoop: 'Point to restart save routine if error occurred 'Display the save as dialog and save file if cancel not selected If FD.Show < 0 Then FD.Execute On Error GoTo 0 Exit Sub ErrorHandler: ' Traps file save error e.g. if file currently in use by another Excel session MsgBox "Please change the file name", vbExclamation, "File Currently In Use" On Error GoTo ErrorHandler GoTo SaveLoop ' Return to Save As dialog End Sub The error trap works fine the first time round, however if I try to overwrite the file a second time I get a run time error 2147467259 (80004005) Cannot Access Planning Spreadsheet.xls I have also tried Err.Clear. How can I reset the error handler? If I hide and reshow the SaveAs dialog, would this clear it? If so, how do I hide the built in dialog as it doesn't seem to have a Hide method? Regards and thanks, Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error trap only works once
ErrorHandler:
On Error GoTo ErrorHandler ''<Get rid of this GoTo SaveLoop ' << use Resume instead of Goto Every error handler must have a Resume unless you are directly exiting the sub. -- Jim "Zarch" wrote in message ... | Hello :-) | | I have some code to save a workbook for the first time using the SaveAs | dialog. | | I need to trap if the same filename is currently being used by aother Excel | session -to prevent VBA from getting upset. | | Essentially the code is: | | Dim FD As FileDialog ' Variable for dialog object | | On Error GoTo ErrorHandler | | 'Create file dialog object | Set FD = Application.FileDialog(MsoFileDialogType.msoFileDi alogSaveAs) | | 'Set the default file name (& path) | FD.InitialFileName = Application.DefaultFilePath & "\Planning Spreadsheet" | | SaveLoop: 'Point to restart save routine if error occurred | 'Display the save as dialog and save file if cancel not selected | If FD.Show < 0 Then FD.Execute | On Error GoTo 0 | Exit Sub | | ErrorHandler: | ' Traps file save error e.g. if file currently in use by another Excel session | MsgBox "Please change the file name", vbExclamation, "File Currently In | Use" | On Error GoTo ErrorHandler | GoTo SaveLoop ' Return to Save As dialog | End Sub | | The error trap works fine the first time round, however if I try to | overwrite the file a second time I get a run time error 2147467259 (80004005) | Cannot Access Planning Spreadsheet.xls | | I have also tried Err.Clear. | | How can I reset the error handler? If I hide and reshow the SaveAs dialog, | would this clear it? If so, how do I hide the built in dialog as it doesn't | seem to have a Hide method? | | Regards and thanks, | | Z |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error trap only works once
That's the reason! - and the solution.
Many thanks Jim. Regards, Z "Jim Rech" wrote: ErrorHandler: On Error GoTo ErrorHandler ''<Get rid of this GoTo SaveLoop ' << use Resume instead of Goto Every error handler must have a Resume unless you are directly exiting the sub. -- Jim "Zarch" wrote in message ... | Hello :-) | | I have some code to save a workbook for the first time using the SaveAs | dialog. | | I need to trap if the same filename is currently being used by aother Excel | session -to prevent VBA from getting upset. | | Essentially the code is: | | Dim FD As FileDialog ' Variable for dialog object | | On Error GoTo ErrorHandler | | 'Create file dialog object | Set FD = Application.FileDialog(MsoFileDialogType.msoFileDi alogSaveAs) | | 'Set the default file name (& path) | FD.InitialFileName = Application.DefaultFilePath & "\Planning Spreadsheet" | | SaveLoop: 'Point to restart save routine if error occurred | 'Display the save as dialog and save file if cancel not selected | If FD.Show < 0 Then FD.Execute | On Error GoTo 0 | Exit Sub | | ErrorHandler: | ' Traps file save error e.g. if file currently in use by another Excel session | MsgBox "Please change the file name", vbExclamation, "File Currently In | Use" | On Error GoTo ErrorHandler | GoTo SaveLoop ' Return to Save As dialog | End Sub | | The error trap works fine the first time round, however if I try to | overwrite the file a second time I get a run time error 2147467259 (80004005) | Cannot Access Planning Spreadsheet.xls | | I have also tried Err.Clear. | | How can I reset the error handler? If I hide and reshow the SaveAs dialog, | would this clear it? If so, how do I hide the built in dialog as it doesn't | seem to have a Hide method? | | Regards and thanks, | | Z |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need error trap | Excel Programming | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Error Trap | Excel Programming | |||
error trap | Excel Programming |