Activeworkbook.saveas Error Handling
Contents |
Way | Trading Add-ins For Excel | Convert Excel Into Calculating Web Pages Excel Web Pages | Produce Clean Efficient VBA Code Every Time | Build Automated Trading Models In Excel | Excel Web excel vba saveas overwrite without prompt Pages | Excel Video Training Forum New Posts FAQ Calendar Forum Actions Mark Forums Read
Activeworkbook.saveas Overwrite
Quick Links Today's Posts What's New? Advanced Search Forum HELP FORUMS Excel General [Solved] VBA: VBA SaveAs Error handling Excel Training /
Vba Saveas Error Handling
Excel Dashboards Reports If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to
Method Saveas Of Object _workbook Failed 1004
proceed. To start viewing messages, select the forum that you want to visit from the selection below. If you don't like Google AdSense in the posts, register or log in above. Click here to view the relaunched Ozgrid newsletter. Results 1 to 3 of 3 Thread: [Solved] VBA: VBA SaveAs Error handling Thread Tools Show Printable Version Search Thread Advanced Search July 26th, 2003 #1 willnjen View Profile View Forum Posts activeworkbook.saveas fileformat I agreed to these rules Join Date 26th July 2003 Posts 3 Hi I am using the following code to save my workbook. fName$ = Application.GetSaveAsFilename ActiveWorkbook.SaveAs Filename:=fName$ _ This works fine except when I try to give the saved file a name that already exists. I get the usual Windows dialog box telling me a file called "example" already exists and do I want to replace it?. If I choose YES it works OK. But if I choose NO, the Macro fails. How do I stop this happening. If I choose NO, I want the macro to finish nicely. Cheers Will Excel Video Tutorials / Excel Dashboards Reports July 26th, 2003 #2 Richie(UK) View Profile View Forum Posts OzMVP Join Date 25th January 2003 Location UK Posts 2,745 Hi Will, Welcome to the board If you want to test whether the file already exists before saving then try something like this: VB: Sub SaveTest() Dim fName As Variant fName = Application.GetSaveAsFilename(filefilter:="Excel Files (*.xls),*.xls") 'get the user-chosen filename If fName = False Then MsgBox "You pressed Cancel!" Exit Sub End If 'we don't want To save If the user cancels If Not FileExists((fName)) Then ActiveWorkbook.SaveAs FileName:=fName Else MsgBox "That filename already exists!" End If 'use UDF To check For file 'if the name doesn't exist
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack workbook.saveas vba Overflow the company Business Learn more about hiring developers or posting ads with us excel vba delete file Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community excel vba check if file exists of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Run time error 1004 for saving excel file (VBA required) up vote 3 down vote favorite http://www.ozgrid.com/forum/showthread.php?t=14730 2 I was wondering if anyone knows how to use vba to save a .txt file that is opened in excel? I have tried writing a coding with a UserForm, but it is giving me errors. I was wondering if it is possible to give user the option to save it at his/her favourite spot, and also his/her favorite name? Public Sub CommandButton1_Click() Dim YesOrNoAnswerToMessageBox As String Dim QuestionToMessageBox As String Dim http://stackoverflow.com/questions/9751622/run-time-error-1004-for-saving-excel-file-vba-required CurrentFile As String QuestionToMessageBox = "Do you want to save?" YesOrNoAnswerToMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, "Save file") If YesOrNoAnswerToMessageBox = vbNo Then Unload Me 'Cancellation command Else CurrentFile = ThisWorkbook.FullName ActiveWorkbook.SaveAs "C:\myfile.xls", FileFormat:=52 Workbooks.Open CurrentFile End If End Sub excel vba excel-vba share|improve this question edited Mar 17 '12 at 22:59 brettdj 38.5k1563109 asked Mar 17 '12 at 16:22 user1204868 3215926 MsgBox returns a Long but you are coercing it into a String. Then you are coercing it back into a Long in order to compare it to vbNo, a Long constant. It would be cleaner to avoid this back-and-forth casting by declaring it as Dim YesOrNoAnswerToMessageBox As Long instead. –Jean-Francois Corbett Mar 19 '12 at 8:51 add a comment| 2 Answers 2 active oldest votes up vote 6 down vote accepted The error is because your file extension (xls) doesn't match your file type (OpenXMLWorkbookMacroEnabled). You would need the xlsm extension. Sub Command1Click() Dim lResp As Long Dim sCurrFile As String Dim sNewFile As String Const sPROMPT As String = "Do you want to save?" Const sFILTER As String = "*.xlsm, *.xlsm" lResp = MsgBox(sPROMPT, vbYesNo, "Save File") If lResp = vbYes Then sCurrFile = ActiveWorkbook.FullName 'save current file name sNewFile = Application.GetSaveAsFilename(, sFILTER) 'get new file name If s
hope there is a really simple answer to this but I have not been abl to find a simple solution to error trapping the ActiveWorkbook.SaveA method. When the following http://www.pcreview.co.uk/threads/error-on-activeworkbook-saveas-method.977695/ command runs (inside a macro) and I selec 'yes' from the dialog box http://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other/excel-2010-vba-run-time-error-1004-with-saveas-if/5432c143-3f5a-e011-8dfc-68b599b31bf5 when it says "the file already exists do want to replace it?" - that's fine. When I select 'no' or 'cancel' th following error occurs... Runtime Error 1004: "Method 'SaveAs' o object '_workbook' failed The method is: (where sWorkBookPath and sFileName are variables for m path and filename) ActiveWorkbook.SaveAs Filename:=sWorkBookPath & "\" excel vba _ & sFileName & ".txt", FileFormat:=xlText, CreateBackup:=False How do I stop the macro crashing when I select 'no' Kindy Regards And -- Message posted from http://www.ExcelForum.com cruisy, May 17, 2004 #1 Advertisements Guest Guest >>try putting in either On Error Resume Next >>O On Error Goto >>Or handle the specific erro On Error GoTo ErrHandler >>>> ErrHandler If Err.Number = 1004 And Err.Description = saveas error handling "Method 'SaveAs' of object '_workbook' failed" The Resume Nex End If >>Make sure you match the description exactly as it shows up in the error ----- cruisy > wrote: ---- Hi there I hope there is a really simple answer to this but I have not been abl to find a simple solution to error trapping the ActiveWorkbook.SaveA method. When the following command runs (inside a macro) and I selec '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' th following error occurs... Runtime Error 1004: "Method 'SaveAs' o object '_workbook' faile The method is: (where sWorkBookPath and sFileName are variables for m path and filename ActiveWorkbook.SaveAs Filename:=sWorkBookPath & "\" & sFileName & ".txt", FileFormat:=xlText, CreateBackup:=Fals How do I stop the macro crashing when I select 'no Kindy Regard And -- Message posted from http://www.ExcelForum.com Guest, May 17, 2004 #2 Advertisements Tim Zych Guest If you always want to save the workbook, this macro won't ask any questions. It will just save. Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=sWorkBookPath & "\" _ & sFileName & ".txt", FileFormat:=xlText, Cre
be down. Please try the request again. Your cache administrator is webmaster. Generated Fri, 30 Sep 2016 02:12:51 GMT by s_hv978 (squid/3.5.20)