+ Reply to Thread
Results 1 to 13 of 13

Need macro to save and close in folder selected by user.

  1. #1
    Forum Contributor
    Join Date
    07-02-2012
    Location
    Florida
    MS-Off Ver
    Home -Excel 2007, Work 2016
    Posts
    254

    Need macro to save and close in folder selected by user.

    Need a macro that would create the file name based on data in the form in these cells:
    C11, C13, C18, and C17
    User would then be required to select the folder where the file would be saved.
    The file would then be saved and closed by the macro - and the user would then be looking at that file - in the folder user selected.
    Thank you!!

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Need macro to save and close in folder selected by user.

    Possibly...
    Please Login or Register  to view this content.
    Assumes the Activesheet is in a different workbook than this code.

  3. #3
    Forum Contributor
    Join Date
    07-02-2012
    Location
    Florida
    MS-Off Ver
    Home -Excel 2007, Work 2016
    Posts
    254

    Re: Need macro to save and close in folder selected by user.

    THANK YOU!!!
    Even if it did not work on first round, I really appreciate your reply!!

    Rcvd a Run-time error ‘438
    Object doesn’t support this property or method

    Highlighted as error –
    With ActiveSheet
    sName = WorksheetFunction.Concat(.Cells(11, 3), .Cells(13, 3), .Cells(18, 3), .Cells(17, 3))

  4. #4
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Need macro to save and close in folder selected by user.

    I don't think Concat is available in xl2007. Try swapping Concat with Concatenate.

  5. #5
    Forum Contributor
    Join Date
    07-02-2012
    Location
    Florida
    MS-Off Ver
    Home -Excel 2007, Work 2016
    Posts
    254

    Re: Need macro to save and close in folder selected by user.

    dangelor - I was hoping you would come back to the problem with the macro you created for me, but I got what you meant to someone else - -i.e "I don't think Concat is available in xl2007. Try swapping Concat with Concatenate."
    Hope you see this!!
    Note - You stated, at the end of macro you wrote, "Assumes the Activesheet is in a different workbook than this code." Not sure what you mean.
    There is a blank sheet user opens, enters data and then runs the macro that is apart of that workbook. Macro gets it's name from data entered by user. User picks the folder but nothing else.
    In addition to saving, file closes. If possible - folder and file just save is on screen at the end

  6. #6
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Need macro to save and close in folder selected by user.

    Let me see if I understand...

    1. User picks the folder where the new file should be saved.
    2. User enters data into the 4 cells (C11, C13, C18, and C17).
    3. These cells are joined together to create the name of file you want to save.
    4. A copy of the active file is saved as the new file name at the location that was chosen.

  7. #7
    Forum Contributor
    Join Date
    07-02-2012
    Location
    Florida
    MS-Off Ver
    Home -Excel 2007, Work 2016
    Posts
    254

    Re: Need macro to save and close in folder selected by user.

    Close & THANK YOU!!
    1. User fills in form (including C11, C13, C18 & C17) and activates macro.
    2 User is shown what the name of the file will be, and if something is wrong, user can halt the process.
    3 If all is OK, user is ask to select the folder in which to file the form.
    4 File is saved in that folder (with the name of what is in C11, C13, C18 & C17) and closed
    5 On screen - the folder in which the file was saved is selected

  8. #8
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Need macro to save and close in folder selected by user.

    Try this...
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    07-02-2012
    Location
    Florida
    MS-Off Ver
    Home -Excel 2007, Work 2016
    Posts
    254

    Re: Need macro to save and close in folder selected by user.

    THANK YOU!!!
    Note - I am 84 and get to work 2 - 3 days a week for a contractor. When I see something that could be done better I try to make it happen.
    Too old to devote hours to learn to code, but young enough to have a clue as to what can be done..
    So, if you look at what you have done for me from my stand point it is a WOW!!
    On what you sent me this time, it did not close so I put in ActiveWorkbook.Close
    but not sure if that is the best place for it or would something else be better?
    ' 5 On screen - the folder in which the file was saved is selected
    Call Shell("explorer.exe" & " " & sPath, vbNormalFocus)
    ActiveWorkbook.Close
    End Select
    End Sub

  10. #10
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Need macro to save and close in folder selected by user.

    Glad it's working for you!You've only got a decade and a few on me. Gives me a look to the future...


    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  11. #11
    Forum Contributor
    Join Date
    07-02-2012
    Location
    Florida
    MS-Off Ver
    Home -Excel 2007, Work 2016
    Posts
    254

    Re: Need macro to save and close in folder selected by user.

    I marked it solved - but that reminded me of a question I meant to ask - is there a way to delete what was a new thread?
    I ask because I made a post but got told by someone I would not fet any feedback because of the way I posted it.
    They were right, so I woulo like to pull it down

  12. #12
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Need macro to save and close in folder selected by user.

    Don't know. Should probably ask one of the moderators.

  13. #13
    Forum Contributor
    Join Date
    07-02-2012
    Location
    Florida
    MS-Off Ver
    Home -Excel 2007, Work 2016
    Posts
    254

    Re: Need macro to save and close in folder selected by user.

    dangelor - I came back to get help on adding spaces and _, $ ssign and Cnk to saved name.
    Just now figued it out - so disregard this post
    Thannks
    Last edited by ILoveStMartin; 03-06-2021 at 03:45 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need to change this macro to save / close instead of allowing user to do so.
    By ILoveStMartin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-02-2021, 11:29 PM
  2. copy checkbox selected listed files from base folder to user selected folder
    By ayush842001 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-16-2020, 08:23 AM
  3. [SOLVED] Macro to save file on any user's folder on the desktop
    By sandeepsandeep in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-18-2020, 12:08 PM
  4. [SOLVED] Need To open and Save a folder location selected by user
    By bfenger in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-07-2016, 05:10 AM
  5. Macro to turn off save box when user click close button
    By Faridwahidi in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 03-22-2016, 07:43 AM
  6. [SOLVED] macro to open all files in a folder, save them, then close them
    By hopefulhart in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-27-2013, 06:40 PM
  7. Need macro to open, refresh, save, close 50 workbooks in a folder.
    By paulcg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-11-2012, 09:23 AM

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