+ Reply to Thread
Results 1 to 12 of 12

Save as macro that specifies file type, file location and takes file name from three cells

  1. #1
    Forum Contributor
    Join Date
    03-06-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    352

    Question Save as macro that specifies file type, file location and takes file name from three cells

    Good Morning,

    I'm after a simple save as macro that does the following:

    - Saves the excel as a macro-free workbook (xls)
    - Takes the file name from three cells (1 of which is a date)
    - The file path is built into the code
    - Doesn't bring up any windows but just saves the sheet as an xls.

    I've searched for this sort of thing and can't seem to find exactly what I want. I could try editing an existing code but haven't yet got a clue how to do that.

    Thanks for any help!

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Save as macro that specifies file type, file location and takes file name from three c

    Please Login or Register  to view this content.
    believe fileformat is 51 for the default but change as you require
    Last edited by humdingaling; 06-13-2013 at 03:11 AM.
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    489

    Re: Save as macro that specifies file type, file location and takes file name from three c

    Just a small doubt here...
    If I remove
    PHP Code: 
    FileFormat:=51
    and in cell 'C1' put the ext also as something like xxxx.xls, will it work?

  4. #4
    Forum Contributor
    Join Date
    03-06-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    352

    Re: Save as macro that specifies file type, file location and takes file name from three c

    Hi and thanks for the very quick reply.

    One small issue - I am wanting to save it in our server, it doesn't like that, saying "invalid call or argument". Any ideas?

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Save as macro that specifies file type, file location and takes file name from three c

    Quote Originally Posted by akhileshgs View Post
    Just a small doubt here...
    If I remove
    PHP Code: 
    FileFormat:=51
    and in cell 'C1' put the ext also as something like xxxx.xls, will it work?
    if you manually put the extension it does not mean it will encode to that file type...ie if you put pdf does not make it a pdf
    that is why you need to use the fileformat number

    Quote Originally Posted by ExcelFailure View Post
    Hi and thanks for the very quick reply.

    One small issue - I am wanting to save it in our server, it doesn't like that, saying "invalid call or argument". Any ideas?
    assume you have amended to your scenario
    can you try it with local drive to see if you get same error?
    also can you post your code so can see if any syntax errors
    Last edited by humdingaling; 06-13-2013 at 04:56 AM.

  6. #6
    Forum Contributor
    Join Date
    03-06-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    352

    Re: Save as macro that specifies file type, file location and takes file name from three c

    It's now saying that it can't save a VB project in a macro-free workbook. That's trying to save it in C drive with the ActiveWorksheet.Copy command removed.

  7. #7
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Save as macro that specifies file type, file location and takes file name from three c

    Quote Originally Posted by ExcelFailure View Post
    - Saves the excel as a macro-free workbook (xls)
    Just to clear xls is not a macro-free environment. Did you mean xlsx?
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  8. #8
    Forum Contributor
    Join Date
    03-06-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    352

    Re: Save as macro that specifies file type, file location and takes file name from three c

    Oooooh sorry yes - just the standard excel workbook

  9. #9
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Save as macro that specifies file type, file location and takes file name from three c

    run it without
    Please Login or Register  to view this content.
    also place the code into personal>module instead of the workbook

  10. #10
    Forum Contributor
    Join Date
    03-06-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    352

    Re: Save as macro that specifies file type, file location and takes file name from three c

    Ok how do I do a personal module?

    And what is the file format number for xlsx?

  11. #11
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Save as macro that specifies file type, file location and takes file name from three c

    for personal module
    http://excelhints.com/2010/11/29/how...acro-workbook/

    most likely as most people would not change their default
    xlWorkbookDefault 51 Workbook default
    http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

  12. #12
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Save as macro that specifies file type, file location and takes file name from three c

    ok i changed code, found error while trying to put date in...(i didnt test with date previously)
    assuming A1 is your date..change as needed

    Please Login or Register  to view this content.

+ 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