Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    VBA - SaveAs (XL97)

    In code, when you do a ActiveWorkbook.SaveAs Filename:="junk", if junk already exists, then the user is prompted "A filed named 'junk' already exists in this location. Do you want to replace it?" Is there a way to capture if the user presses No and/or Cancel? Is there a difference in what Excel does with a No vs. a Cancel? TIA --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA - SaveAs (XL97)

    I don't think Excel VBA provides feedback on the result of SaveAs. You could test ActiveWorkvook.Name or ActiveWorkbook.FullName to see if the name has changed.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA - SaveAs (XL97)

    Sam

    I addition to Hans's reply

    I usually time stamp my Save As files

    ActiveWorkbook.SaveAs Filename:="Jezza" & Format(Now(), "YYMMDDHHMM")

    to stop me overwriting by accident
    Jerry

  4. #4
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA - SaveAs (XL97)

    If you'd like the user to not see the "Do you wish to replace..." dialogue box at all, you can do this by using DisplayAlerts:
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "junk"
    Application.DisplayAlerts = True

    This will overwrite the existing "junk" (if there is one) without the user being prompted at all.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA - SaveAs (XL97)

    <hr>You could test ActiveWorkvook.Name or ActiveWorkbook.FullName to see if the name has changed.<hr>
    Good thought, but you have to use On Error to continue, so you already know that the user has pressed No or Cancel. Dosen't seem to me that there is a difference between No and Cancel.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VBA - SaveAs (XL97)

    Sam,
    I would check for the existence of the file first. If it's not there, run the saveas; if it is, pop up a small form offering the choice of replacing. That way, you can trap exactly what the response is. I don't think there is a way of distinguishing No and Cancel responses to the built-in dialog.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Re: VBA - SaveAs (XL97)

    I'm trying to use this idea with quitting Excel. At the end of my Sub(Main) I have the following code.
    Application.DisplayAlerts = False
    Application.Quit
    Application.DisplayAlerts = True
    End Sub
    I still get propted to save files, and am getting some error messages that I've noticed when closing some of the open windows. Any ideas?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA - SaveAs (XL97)

    What error messages?

  9. #9
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Re: VBA - SaveAs (XL97)

    Firstly, it asks me to save the file I've just saved. I choose No. Then I get (2 times) "The picture is too large and will be truncated." [OK] -- I think this comes from one of the files I've loaded up and copied a sheet to the master from. Then "Excel cannot complete this task with available resources. Choose less data or close other applications." [OK] After choosing this OK, Excel closes. All I'm doing with the various open files is copying data from them to the master template (the first one asked to be saved).

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA - SaveAs (XL97)

    Try closing (and saving if necessary) all open workbooks (except the one containing the macro). If you want to save the workbook containing the macro, do so too, else set its Saved property to True. Then quit Excel.

Posting Permissions

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