+ Reply to Thread
Results 1 to 7 of 7

Runtime Error '1004' Method 'SaveAs' of object'_Workbook Failed

  1. #1
    Registered User
    Join Date
    07-04-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    16

    Runtime Error '1004' Method 'SaveAs' of object'_Workbook Failed

    Hi Everyone
    This Forum has been a life saver on numerous occasions.
    The code that follows has been courtesy of this forum and the extensive help i received from everyone.

    But after an year of using the following code it is giving me the following error:

    Runtime Error '1004' Method 'SaveAs' of object'_Workbook Failed

    It worked for a while ( 1 year) and then suddenly failed.

    A little background on the code.

    The code basically is suppose to compare the system date (date on your computer) to a date on the excel file. If the system year is larger than date on the excel file then the system will complete a sort function and then save the file in the following locations myPath = "S:\AdminDocs\Finance\Purchasing\Common\Non-Conforming POs-Database\Archive" in other words does a backup.


    The system does the same for month and for date. So if system year is same as excel file year then we compare the month, and if month on the system is higher then the system does a sort and a backup.

    That's where the SaveAs runtime error comes in. As far as I know that's where it is failing. I am a bit confused what is causing this problem.

    I would really appreciate help, I am so frustrated.

    Thanks
    Attached Files Attached Files
    Last edited by Angsome; 09-12-2014 at 12:30 PM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,088

    Re: Runtime Error '1004' Method 'SaveAs' of object'_Workbook Failed

    I think you need to change

    FileFormat:=1

    to

    FileFormat:=51
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    07-04-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Runtime Error '1004' Method 'SaveAs' of object'_Workbook Failed

    Hi Bernie
    Thanks for the reply

    I have tried them all. Here is a list of alternatives I have tried thus far. Starting with the most recent. So 51 was my original number.

    '.SaveAs myPath

    '.SaveAs myPath, FileFormat:=0

    '.SaveAs "S:\AdminDocs\Finance\Purchasing\Common\Non-Conforming POs-Database\Archive1\sName.xlsx"

    ' .SaveAs myPath _
    & "\" & sName & ".xlsx", _
    FileFormat:=1

    '.SaveAs myPath _
    & "\" & sName & ".xlsx", _
    FileFormat:=51

    Hope this helps and I hope you can help out

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Runtime Error '1004' Method 'SaveAs' of object'_Workbook Failed

    Where is your macro located? Are you trying to save Thisworkbook?

  5. #5
    Registered User
    Join Date
    07-04-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Runtime Error '1004' Method 'SaveAs' of object'_Workbook Failed

    my location of folder location was wrong. Thanks for the advice everyone.

  6. #6
    Registered User
    Join Date
    07-04-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Runtime Error '1004' Method 'SaveAs' of object'_Workbook Failed

    how do i mark it as solved gentleman?

  7. #7
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Runtime Error '1004' Method 'SaveAs' of object'_Workbook Failed

    Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1