Page 4 of 4 FirstFirst ... 234
Results 31 to 32 of 32

Thread: Save a copy of a workbook with a new name
Thanks Thanks: 0 Likes Likes: 0

  1. #31
    Board Regular AndyTampa's Avatar
    Join Date
    Aug 2011
    Location
    Brandon, FL
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    Quote Originally Posted by BlakeSkate View Post
    no? i don't see why you would have used your solution over my initial one on this post if you were not wanting the dialogue boxes?
    The solution you referenced shows the path to be a static path. We've moved on to include a way to pick a location to save the file. However, this dialog box shows a field with the filename and gives the user the ability to pick a filename from the list of files in the dialog box either on purpose or accidentally. I'm not sure I like that. I'm trying to idiot-proof this as much as possible since I might give it to someone else to use. I didn't say I didn't want the dialog box. I said I wanted to eliminate two fields from the dialog box so that they would only choose a folder in which to save the file for which they've already chosen a name.

    Quote Originally Posted by BlakeSkate View Post
    to my knowledge you can't restrict the basic functionality of a save as box, just the options it allows like selecting multiple files etc.
    if you were wanting someone to select a folder to save in you could use file picker in the same kind of way we are using strResult
    That answered one of my questions. We can't eliminate those fields. (Right?)

    I haven't played with the code you just wrote yet. I will soon. But just know that I have been using the code you provide as well as others I've found to get a solution that works. Now I'm trying to improve upon it. With what I'm learning, or rather finding, I'm creating a better idea of what I want in the end.

    As it stands now, what I'm aiming for is:
    1. Determine the location of the original file to have a default path for the dialog box.
    2. Determine the file type of the original file so the newly created file will be saved as the same type.
    3. Ask for a filename using an InputBox that contains instruction text similar to what I've been using.
    4. Ask for a folder in which to save the file. This dialog should only show folders and use the default path from #1 .
    5. Copy all sheets to a new workbook.
    6. Save the new workbook using the path, name, and extension from #4 , #3 , and #2 .
    7. If there is no selection or Cancel is pressed in either of the dialog boxes, then close the new workbook and end the macro.
    8. Once the new workbook is saved, it should close.

    I'll post again once I've tried out the latest code you've posted. I'm having a little difficulty following it so far.

  2. #32
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    Quote Originally Posted by AndyTampa View Post
    I didn't say I didn't want the dialog box. I said I wanted to eliminate two fields from the dialog box so that they would only choose a folder in which to save the file for which they've already chosen a name.
    then the newest code i wrote should accomplish this

    Quote Originally Posted by AndyTampa View Post
    That answered one of my questions. We can't eliminate those fields. (Right?)
    well not with save as, but filepicker has a different mechanic that doesn't involve the things you're talking about (thus i wrote it as the solution i my last post)


    Quote Originally Posted by AndyTampa View Post
    1. Determine the location of the original file to have a default path for the dialog box.
    i currently have the set one to go to default (which is usually documents). To go to where the workbook is located replace the line with .InitialFileName =ThisWorkbook.Path & "/"


    Quote Originally Posted by AndyTampa View Post
    2. Determine the file type of the original file so the newly created file will be saved as the same type.
    I'm unaware of how to do this but the internet is a vast place.

    Quote Originally Posted by AndyTampa View Post
    7. If there is no selection or Cancel is pressed in either of the dialog boxes, then close the new workbook and end the macro.
    i would need to put in error handling for the folder picker, but i would like to know if its what you're looking for first beforehand
    Last edited by BlakeSkate; Aug 23rd, 2019 at 10:41 AM.
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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