+ Reply to Thread
Results 1 to 7 of 7

Macro to ask to rename the file if one exists in the same location

  1. #1
    Registered User
    Join Date
    06-08-2009
    Location
    Annapolis, Maryland
    MS-Off Ver
    Excel 2007
    Posts
    85

    Macro to ask to rename the file if one exists in the same location

    Good day all.

    I seem to be having a trial making this issue easy and productive for the users. First, I have a macro on the LEAD file and will open an ESTIMATING template. The name of the LEAD file is inserted within the ESTIMATE.

    When a button is clicked in the ESTIMATE template, it will save within the same folder as the LEAD file...and actually two folders deeper which is perfect.

    I currently have the macro set so that the ESTIMATE template saves with the same name as the LEAD folder but adding 'EST' on the end. That's fine. Then if the button is clicked again from the template, it will add a 2 tot he file name, then a 3, etc.

    Here's the code --

    Please Login or Register  to view this content.
    Is there any way I could change this so if a file already exists, a popup will ask if the user wants to save it as another file name instead of just adding the 2 or 3 on the end?

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Macro to ask to rename the file if one exists in the same location

    Maybe something based around ...

    Please Login or Register  to view this content.
    Martin

  3. #3
    Registered User
    Join Date
    06-08-2009
    Location
    Annapolis, Maryland
    MS-Off Ver
    Excel 2007
    Posts
    85

    Re: Macro to ask to rename the file if one exists in the same location

    Thanks for your help, mrice. I'm afraid I'm not knowledgeable enough to know where to put your code...or what to replace it with.

  4. #4
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Macro to ask to rename the file if one exists in the same location

    Here's a more complete vesion - difficult to test without having the actual workbook to work with.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-08-2009
    Location
    Annapolis, Maryland
    MS-Off Ver
    Excel 2007
    Posts
    85

    Re: Macro to ask to rename the file if one exists in the same location

    Martin, thanks again! The code gets me to the popup that a file already exists, however, when I click OK (to save it under another name), I get a 404 error message.

  6. #6
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Macro to ask to rename the file if one exists in the same location

    What does the error message say and which line of code is highlighted in yellow if you hit the debug button?

  7. #7
    Registered User
    Join Date
    06-08-2009
    Location
    Annapolis, Maryland
    MS-Off Ver
    Excel 2007
    Posts
    85

    Re: Macro to ask to rename the file if one exists in the same location

    Martin, I changed vbCancel to vbOK and it worked. Unfortunately, I'm having to take a different route. It now makes more sense to have the numbers accrue on the LEAD sheet rather than the ESTIMATING template.

    So I need to play with this on my own a little more *sigh*.

    I appreciate your help. You have a great day.

    Debbie

+ 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