+ Reply to Thread
Results 1 to 25 of 25

Saving an Excel File

  1. #1
    Registered User
    Join Date
    12-06-2011
    Location
    Oslo
    MS-Off Ver
    Excel 2003
    Posts
    66

    Saving an Excel File

    I was just thinking if this can be achieved?
    Cell A1 has some date, I want the code that can create a copy of the excel file and save it as "PS+thedatein Cell A1".xls

    For Example:

    If A1=01042012
    then the file should get saved as PS01042012

  2. #2
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    1,955

    Re: Saving an Excel File

    try this and see if it helps you

    Please Login or Register  to view this content.
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

  3. #3
    Registered User
    Join Date
    12-06-2011
    Location
    Oslo
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Saving an Excel File

    I'm seeing the below mentioned error message:

    Run time error '1004'
    Method 'SaveAs' of object_'Workbook' failed.

    please help...

  4. #4
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    1,955

    Re: Saving an Excel File

    hi,
    please see http://www.vbaexpress.com/kb/getarticle.php?kb_id=38

    Please Login or Register  to view this content.
    Last edited by john55; 01-04-2012 at 10:52 AM.

  5. #5
    Registered User
    Join Date
    12-06-2011
    Location
    Oslo
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Saving an Excel File

    Hi John,

    The example provided on the mentioned URL is working fine, however, the code provided by you is still showing some error.

    I've tried changing the date format as well the location to save the file but no success.

    Can you please attach a working example? It will be gr8 for me

  6. #6
    Forum Expert
    Join Date
    12-15-2009
    Location
    Homeless. I am not kidding.
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,134

    Re: Saving an Excel File

    Try. I think it will give you an error if cell is in date format only

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    12-06-2011
    Location
    Oslo
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Saving an Excel File

    Run time error '1004'
    Method 'SaveAs' of object_'Workbook' failed.

    Getting the above mentioned errror message

  8. #8
    Forum Expert
    Join Date
    12-15-2009
    Location
    Homeless. I am not kidding.
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,134

    Re: Saving an Excel File

    Change the file path to the folder's directory address you wish to save. The reason why you're running into the error is because the workbook that you're running the macro from, is a brand new workbook, and since it is not saved it does not have a file path, yet.

    FileName = ActiveWorkbook.Path to "C:\Users\test\abc\whatever

  9. #9
    Registered User
    Join Date
    12-06-2011
    Location
    Oslo
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Saving an Excel File

    Still not working, if possible, please attach a working example.

  10. #10
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    1,955

    Re: Saving an Excel File

    hi Piggy,
    see file attached with JieJen's code, it works for me.
    (not sure what it's going wrong there...)
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-06-2011
    Location
    Oslo
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Saving an Excel File

    Hey John,

    The moment I execute this code - I get a new file called "Book 1".

    I want it to have a specific name i.e. PS+the date in Cell A1. The new should get saved and close automatically.

  12. #12
    Registered User
    Join Date
    12-06-2011
    Location
    Oslo
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Saving an Excel File

    http://www.vbaexpress.com/kb/getarticle.php?kb_id=38

    This example is perfect but it is other way round.

    I've Date in Cell A1

  13. #13
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    1,955

    Re: Saving an Excel File

    hi Pigggy,
    hope this new one helps you
    Please Login or Register  to view this content.
    also see http://www.mrexcel.com/archive/VBA/19609d.html

  14. #14
    Registered User
    Join Date
    12-06-2011
    Location
    Oslo
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Saving an Excel File

    I'm getting the popup that the file is being saved with the desired name, however, I'm not able to find the file on the mentioned drive.

    Where it is getting saved?

  15. #15
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    1,955

    Re: Saving an Excel File

    hi,
    Please test it with a workbook which has a name, not with a new one without name, then add in cell A1 yr date or whatever!
    it should be on yr desktop or in My Documents.
    Last edited by john55; 01-07-2012 at 03:37 AM.

  16. #16
    Registered User
    Join Date
    12-06-2011
    Location
    Oslo
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Saving an Excel File

    I have saved the file, macro execution is flaw-less. But it is not generating the file.

    Try executing the above mentioned code.

  17. #17
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    1,955

    Re: Saving an Excel File

    try this test file, format yr cell A1 as text and type yr data as (as an example) 08.jan.2012
    Attached Files Attached Files

  18. #18
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    1,955

    Re: Saving an Excel File

    hi, thx for rep!
    If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.

  19. #19
    Registered User
    Join Date
    12-06-2011
    Location
    Oslo
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Saving an Excel File

    Quote Originally Posted by john55 View Post
    hi, thx for rep!
    If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.

    John, please check cell B1, it is the format that I'm going to have & name of the should be PS+B1.

    cell B1 - will keep changing everyday.
    Attached Files Attached Files

  20. #20
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    1,955

    Re: Saving an Excel File

    hi,
    you have nothing in cell b1, I added 11.jan.2012 in cell b1 and pls check the attached file.
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    12-06-2011
    Location
    Oslo
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Saving an Excel File

    Ooops....check cell B2, that is the format - and I wanna save the file to the d: drive

  22. #22
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    1,955

    Re: Saving an Excel File

    hi again Pigggy!
    add a new folder in D (as my example "test1") and try it
    Please Login or Register  to view this content.
    Attached Files Attached Files

  23. #23
    Registered User
    Join Date
    12-06-2011
    Location
    Oslo
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Saving an Excel File

    Attached error message that I'm encountering, have created the folder on the d: drive

    This is where we started.

    Check execution of this macro on your end.
    Attached Images Attached Images

  24. #24
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    1,955

    Re: Saving an Excel File

    what can I say...
    I change yr name as ...se attached file!
    Attached Files Attached Files

  25. #25
    Registered User
    Join Date
    12-06-2011
    Location
    Oslo
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Saving an Excel File

    You're changing the format of the cell, from date to text, thats why you are able to execute it.

    I cannot change the format of the cell, it has to be date only.

+ 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