'Save as' dialog opens despite Application.DisplayAlerts = False

Discussion in 'Excel Programming' started by Poniente, Jun 4, 2011.

  1. Poniente

    Poniente Guest

    I'm using the following code to save a workbook under a unique name in
    a single directory. This code runs on several instances of excel.
    Often these save commands happen at the same time.

    <begin of code>
    On Error GoTo RetrySaveHandler
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=SaveName, Password:="",
    WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = True

    About 5 times a day, a 'Save as' dialog box opens asking for a file
    name, with random looking default filename filled out, like:
    and like
    (i.e. not anything like the SaveName I specified and even without
    the .xls extention)

    If I then manually set the pointer to 'SaveName', it will save without
    a problem.

    Unfortunately, the errorhandler doesn't catch the above error.
    Is there anyone who understands what is the problem (and/or has an
    idea how to solve it ;-) )?

    Poniente, Jun 4, 2011
    1. Advertisements

  2. Poniente

    Poniente Guest

    Solved :), (to whom it may concern ;-) )

    Each instance saves to its own temporary directory and then moves the
    file to the final directory.

    Regards, Poniente
    Poniente, Jun 24, 2011
    1. Advertisements

  3. Poniente

    GS Guest

    After serious thinking Poniente wrote :
    Sorry I didn't read your post earlier! I'm familiar with this issue and
    have found my own solution as follows:

    With Application
    .EnableEvents = False: .DisplayAlerts = False
    End With

    'save and/or close files

    With Application
    .EnableEvents = True: .DisplayAlerts = True
    End With

    Does this help?
    GS, Jun 24, 2011
  4. Poniente

    GS Guest

    It happens that GS formulated :
    I failed to mention that the SaveAs Filename MUST include the full path
    or Excel will display the SaveAs dialog to prompt you for it. IOW, your
    SaveName variable should be "save_to_folder_path\" and "filename".
    GS, Jun 24, 2011
  5. Poniente

    Poniente Guest

    Hi Garry,
    Thanks for your suggestion! I looked into EnableEvents and it apears
    to me that all kinds of events will 'disabled' by this line... so I
    stick to the the solution with the separate temporary directories..
    (which works fine, ... so far ;-) )
    Poniente, Jun 26, 2011
  6. Poniente


    i cant saveas with thesaveas prompt window keep showing i tried everything please help
    , May 4, 2014
  7. Poniente

    GS Guest

    i cant saveas with thesaveas prompt window keep showing i tried
    Please show your code!


    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    GS, May 4, 2014
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.