Activeworkbook.saveas Error 1004
Contents |
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 Overflow the company Business method saveas of object _workbook failed 1004 Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation method saveas of object _workbook failed 2010 Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like
Runtime Error 1004 Saveas Method Of Workbook Class Failed
you, helping each other. Join them; it only takes a minute: Sign up Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed up vote 0 down vote favorite 1 Users have an Excel spreadsheet that when they go
Saveas Method Of Workbook Class Failed Vba
to save it, they press a button which runs the below vba code within a macro. The script attempts to save the Excel spreadsheet to a network location amending the file name with today's date. Intermittently the script will fail with "Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed". Does anyone know what the cause is? The script is: Public Sub Copy_Save_R2() Dim wbNew As Workbook Dim fDate As Date fDate = Worksheets("Update").Range("D3").Value Set wbNew = method 'saveas' of object '_workbook' failed excel 2010 ActiveWorkbook With wbNew ActiveWorkbook.SaveAs Filename:="Q:\R2 Portfolio Prints\#Archive - R2 Portfolio\" & "R2 Portfolio - CEC A " & Format(fDate, "mm-dd-yyyy") End With Sheets("Update").Activate End Sub excel vba excel-vba share|improve this question edited Aug 21 '15 at 11:17 mielk 3,188517 asked Aug 21 '15 at 11:14 luligoy 111 1 Basically; your code cannot access the mapped drive –Hugo Yates Aug 21 '15 at 11:23 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote As Hugo stated, it could be an issue with the mapped drive. I prefer to use the full UNC path (\Thismachine...), in case the workbook gets used on a machine that doesn't have the mapped drive set up. I thought the missing extension could be the problem, but I just tested it in Excel 2013 and it automatically added .xlsx to the filename. The issue is probably due to the wbNew reference. It's completely unnecessary and should not be combined with ActiveWorkbook. Basically, you should have either a reference to a workbook, or use the predefined ActiveWorkbook reference. I'd also recommend using ThisWorkbook instead, since the user might click on another book while code is running. Public Sub Copy_Save_R2() Dim wbNew As Workbook Dim fDate As Date fDate = Worksheets("Update").Range("D3").Value Application.DisplayAlerts = False ThisWorkbook.SaveAs Filename:="Q:\R2 Portfolio Prints\#Archive - R2 Portfolio\R2 Portfolio - CEC A "
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
Excel Vba Error 1004 Document Not Saved
Us Learn more about Stack Overflow the company Business Learn more about hiring xlopenxmlworkbookmacroenabled developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the activeworkbook.saveas fileformat Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Getting “method saveas of object _workbook http://stackoverflow.com/questions/32138908/run-time-error-1004-method-saveas-of-object-workbook-failed failed” error while trying to save an XLSM as CSV up vote 4 down vote favorite 1 I'm trying to save a (macro-enabled) excel workbook as a csv file via this macro, overwriting the old one (below I had the change the name of the folder and the Sheet, but that doesn't seem to be the issue!): Sub SaveWorksheetsAsCsv() Dim SaveToDirectory As String Dim CurrentWorkbook As String http://stackoverflow.com/questions/28967762/getting-method-saveas-of-object-workbook-failed-error-while-trying-to-save-an Dim CurrentFormat As Long CurrentWorkbook = ThisWorkbook.FullName CurrentFormat = ThisWorkbook.FileFormat SaveToDirectory = "\MyFolder\" Application.DisplayAlerts = False Application.AlertBeforeOverwriting = False Sheets("My_Sheet").Copy ActiveWorkbook.SaveAs Filename:=SaveToDirectory & "My_Sheet" & ".csv", FileFormat:=xlCSV ActiveWorkbook.Close SaveChanges:=False ThisWorkbook.Activate ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat Application.DisplayAlerts = True Application.AlertBeforeOverwriting = True End Sub Oddly enough, sometimes, but not always, it fails and the error message of the title (Runtime Error 1004: method saveas of object _workbook failed) pops out with the debugger pointing me out this row: ActiveWorkbook.SaveAs Filename:=SaveToDirectory & "My_Sheet" & ".csv", FileFormat:=xlCSV I googled it up and apparently it does happen a lot to people and some of the solutions I tried were: Specifiying that the directory is a string Avoid any special character in the file name or folder (seen here) Copy paste the worksheet as value before saving it as .csv (seen here) Specifying the FileFormat with the .csv code number (seen here) Disabling/Re-enabling some of the alerts Adding other fields in the ActiveWorkbook.SaveAs row, regarding passwords, creating backups etcetc Still, it might run correctly up to 50-60 times in a row, and then at some point fail again at the row. Any suggetion or things I could look up to solve this problem (except
Forums Excel Questions Error 1004 trying to save active workbook Results 1 to 7 of 7 Error 1004 trying to save active workbookThis is a discussion on Error 1004 trying to save active workbook within the Excel Questions forums, part of the Question Forums http://www.mrexcel.com/forum/excel-questions/547194-error-1004-trying-save-active-workbook.html category; I have a macro that runs fine using Excel 2007 on Windows XP, but with http://www.vbaexpress.com/forum/showthread.php?39523-Solved-ActiveWorkbook-SaveAs-xlsm-help! Excel 2010 on Windows 7 ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode May 1st, 2011,02:56 PM #1 littlebob New Member Join Date Apr 2011 Posts 6 Error 1004 trying to save active workbook I have error 1004 a macro that runs fine using Excel 2007 on Windows XP, but with Excel 2010 on Windows 7 I get a 'Runtime error 1004' from the following line of code: ActiveWorkbook.SaveAs Filename:="Teams-" & Range("A2").Value, _ FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False Am I overlooking something simple? Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote May 1st, 2011,03:09 PM #2 VoG MrExcel MVP Join Date Jun 2002 Location 127.0.0.1 Posts 63,652 Re: Error 1004 trying to save active workbook look here method saveas of http://www.rondebruin.nl/saveas.htm Share Share this post on Digg Del.icio.us Technorati Twitter HTH, Peter Please test any code on a copy of your workbook. Reply With Quote May 1st, 2011,03:59 PM #3 littlebob New Member Join Date Apr 2011 Posts 6 Re: Error 1004 trying to save active workbook Perhaps I'm missing something in the above answer, but the failing SaveAs statement does have the FileFormat specified. This isn't a 2003 vs 2010 issue, the code runs on Excel 2007 but not on 2010. Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote May 1st, 2011,04:37 PM #4 Norie Board Regular Join Date Apr 2004 Location Stirling Posts 69,870 Re: Error 1004 trying to save active workbook Does the filename you are trying to save as have the correct extension? Share Share this post on Digg Del.icio.us Technorati Twitter If posting code please use code tags. Reply With Quote May 1st, 2011,09:21 PM #5 littlebob New Member Join Date Apr 2011 Posts 6 Re: Error 1004 trying to save active workbook I guess I'm not posting my problem/question properly. (I'm new at this) The problem is that a line of code in one of my macros runs fine on my desktop (running XP and Excel 2007) but gives me an error 1004 when I try to run the same macro on a system running Windows 7 with Excel 2010. I guess my question is: Is there a compatibili
VBA Code & Other Help Excel Help Solved: ActiveWorkbook.SaveAs xlsm help! 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 proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 7 of 7 Thread: Solved: ActiveWorkbook.SaveAs xlsm help! Thread Tools Show Printable Version Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 10-22-2011,08:00 AM #1 Andybuck86 View Profile View Forum Posts View Blog Entries View Articles VBAX Regular Joined Aug 2010 Posts 32 Location Solved: ActiveWorkbook.SaveAs xlsm help! You guys have helped me out plenty of times in the past and I'm hoping you can do the same again for me! I have the following code as a module in my workbook Option Explicit Sub Save_Password() Application.DisplayAlerts = False Dim Open_Password As String Dim New_File_Name As String New_File_Name = Sheets("QryData").Range("J32").Value & "\" & Sheets("QryData").Range("J31").Value & ".xlsm" Open_Password = InputBox("Please enter a password") ActiveWorkbook.SaveAs New_File_Name, _ WriteResPassword:=Open_Password, _ Password:="test" ' ActiveWorkbook.SaveAs New_File_Name, _ ' creates a new file with the name in G1 ' Password:=Open_Password, _ ' this is the password the user enters ' WriteResPassword:="password" ' this is the master password End Sub Formatting tags added by mark007 This worked great until I decided I wanted to save my spreadsheet as a template. After this when I run my code I get a run-time error 1004 I have searched forums and think I need to add something like this : fileformat:=52 52 = xlOpenXMLWorkbookMacroEnabled But I keep getting compile errors... Any ideas? Reply With Quote 10-22-2011,01:53 PM #2 MCraiss View Profile View Forum Posts View Blog Entries View Articles VBAX Newbie Joined Oct 2011 Posts 2 Location It seems to me, that this has something to do with access rights. When I execute the following command on my Win7 machine I also get an error 1004: ActiveWorkbook.SaveAs "c:\test.xlsm", 52 If I change the path to some directory where you don't need administrative rights to save/delete files the same code works without trouble: ActiveWorkbook.SaveAs "c:\data\test.xlsm", 52 This happens although I am logged in as administrator on my machine! May be you could try to change your path too. Hope this helps Michael Reply With Quote 10-22-2011,03: