Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error on ActiveWorkbook.SaveAs method
Hi there,
I hope there is a really simple answer to this but I have not been able to find a simple solution to error trapping the ActiveWorkbook.SaveAs method. When the following command runs (inside a macro) and I select 'yes' from the dialog box when it says "the file already exists do want to replace it?" - that's fine. When I select 'no' or 'cancel' the following error occurs... Runtime Error 1004: "Method 'SaveAs' object '_workbook' failed. ActiveWorkbook.SaveAs Filename:=sWorkBookPath & "\" _ & sFileName & ".txt", FileFormat:=xlText, CreateBackup:=False How can I stop the macro crashing when I select 'no'? Thanks, Louis |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error on ActiveWorkbook.SaveAs method
You have a few choices...
#1. Just overwrite the existing workbook. application.displayalerts = false activeworkbook.saveas .... application.displayalerts = true #2. Check for the existing file, then ask the user for direction. dim Resp as long resp = vbyes if dir(sWorkBookPath & "\" & sFileName & ".txt") = "" then 'it's not there else resp = msgbox(Prompt:="Overwrite the existing file?"), _ buttons:=vbyesno) end if if resp = vbyes then application.displayalerts = false activeworkbook.saveas .... application.displayalerts = true else msgbox "ok, not saved!" end if #3. Handle the error yourself. on error resume next activeworkbook.saveas ... if error.number < 0 then err.clear msgbox "not saved" else msgbox "saved" end if on error goto 0 ll wrote: Hi there, I hope there is a really simple answer to this but I have not been able to find a simple solution to error trapping the ActiveWorkbook.SaveAs method. When the following command runs (inside a macro) and I select 'yes' from the dialog box when it says "the file already exists do want to replace it?" - that's fine. When I select 'no' or 'cancel' the following error occurs... Runtime Error 1004: "Method 'SaveAs' object '_workbook' failed. ActiveWorkbook.SaveAs Filename:=sWorkBookPath & "\" _ & sFileName & ".txt", FileFormat:=xlText, CreateBackup:=False How can I stop the macro crashing when I select 'no'? Thanks, Louis -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error on ActiveWorkbook.SaveAs method
On Jul 27, 10:43 am, Dave Peterson wrote:
You have a few choices... #1. Just overwrite the existing workbook. application.displayalerts = false activeworkbook.saveas .... application.displayalerts = true #2. Check for the existing file, then ask the user for direction. dim Resp as long resp = vbyes if dir(sWorkBookPath& "\" & sFileName & ".txt") = "" then 'it's not there else resp = msgbox(Prompt:="Overwrite the existing file?"), _ buttons:=vbyesno) end if if resp = vbyes then application.displayalerts = false activeworkbook.saveas .... application.displayalerts = true else msgbox "ok, not saved!" end if #3. Handle the error yourself. on error resume next activeworkbook.saveas ... if error.number < 0 then err.clear msgbox "not saved" else msgbox "saved" end if on error goto 0 ll wrote: Hi there, I hope there is a really simple answer to this but I have not been able to find a simple solution to error trapping the ActiveWorkbook.SaveAs method. When the following command runs (inside a macro) and I select 'yes' from the dialog box when it says "the file already exists do want to replace it?" - that's fine. When I select 'no' or 'cancel' the following error occurs... Runtime Error 1004: "Method 'SaveAs' object '_workbook' failed. ActiveWorkbook.SaveAs Filename:=sWorkBookPath& "\" _ & sFileName & ".txt", FileFormat:=xlText, CreateBackup:=False How can I stop the macro crashing when I select 'no'? Thanks, Louis -- Dave Peterson Thanks Dave, In the second option, using sWorkBookPath and sFileName, are those two variables pre-defined, or do I need to establish them as string variables? Thanks Louis |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error on ActiveWorkbook.SaveAs method
Those are variables. You'll want to declare them and assign the values you want
to them. ll wrote: Thanks Dave, In the second option, using sWorkBookPath and sFileName, are those two variables pre-defined, or do I need to establish them as string variables? Thanks Louis -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SaveAs method error | Excel Discussion (Misc queries) | |||
Error When using ActiveWorkBook.SaveAs and EnableEvents in same macro | Excel Programming | |||
Error When using ActiveWorkBook.SaveAs in Excel | Excel Programming | |||
Error on ActiveWorkbook.SaveAs method | Excel Programming | |||
Error on ActiveWorkbook.SaveAs method | Excel Programming |