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

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

  1. Poniente

    Poniente Guest

    Hi,
    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>
    RetrySaveHandler:
    On Error GoTo RetrySaveHandler
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=SaveName, Password:="",
    WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = True
    <eoc>

    About 5 times a day, a 'Save as' dialog box opens asking for a file
    name, with random looking default filename filled out, like:
    7D2E8000
    and like
    9AB6B000
    (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 ;-) )?

    Regards,
    Poniente
     
    Poniente, Jun 4, 2011
    #1
    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
    #2
    1. Advertisements

  3. Poniente

    GS Guest

    After serious thinking Poniente wrote :
    > Hi,
    > 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>
    > RetrySaveHandler:
    > On Error GoTo RetrySaveHandler
    > Application.DisplayAlerts = False
    > ActiveWorkbook.SaveAs Filename:=SaveName, Password:="",
    > WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    > Application.DisplayAlerts = True
    > <eoc>
    >
    > About 5 times a day, a 'Save as' dialog box opens asking for a file
    > name, with random looking default filename filled out, like:
    > 7D2E8000
    > and like
    > 9AB6B000
    > (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 ;-) )?
    >
    > Regards,
    > Poniente


    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?

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    ClassicVB Users Regroup! comp.lang.basic.visual.misc
     
    GS, Jun 24, 2011
    #3
  4. Poniente

    GS Guest

    It happens that GS formulated :
    > After serious thinking Poniente wrote :
    >> Hi,
    >> 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>
    >> RetrySaveHandler:
    >> On Error GoTo RetrySaveHandler
    >> Application.DisplayAlerts = False
    >> ActiveWorkbook.SaveAs Filename:=SaveName, Password:="",
    >> WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    >> Application.DisplayAlerts = True
    >> <eoc>
    >>
    >> About 5 times a day, a 'Save as' dialog box opens asking for a file
    >> name, with random looking default filename filled out, like:
    >> 7D2E8000
    >> and like
    >> 9AB6B000
    >> (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 ;-) )?
    >>
    >> Regards,
    >> Poniente

    >
    > 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?


    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".

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    ClassicVB Users Regroup! comp.lang.basic.visual.misc
     
    GS, Jun 24, 2011
    #4
  5. Poniente

    Poniente Guest

    On 24 jun, 16:10, GS <> wrote:
    > It happens that GS formulated :
    >
    >
    >
    >
    >
    >
    >
    >
    >
    > > After serious thinkingPonientewrote :
    > >> Hi,
    > >> 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>
    > >>   RetrySaveHandler:
    > >>   On Error GoTo RetrySaveHandler
    > >>   Application.DisplayAlerts = False
    > >>   ActiveWorkbook.SaveAs Filename:=SaveName, Password:="",
    > >> WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    > >>   Application.DisplayAlerts = True
    > >> <eoc>

    >
    > >> About 5 times a day, a 'Save as' dialog box opens asking for a file
    > >> name, with random looking default filename filled out, like:
    > >>   7D2E8000
    > >> and like
    > >>   9AB6B000
    > >> (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 ;-) )?

    >
    > >> Regards,
    > >>Poniente

    >
    > > Sorry I didn't read your post earlier! I'm familiar with this issue andhave
    > > 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?

    >
    > 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".
    >
    > --
    > Garry
    >
    > Free usenet access athttp://www.eternal-september.org
    > ClassicVB Users Regroup! comp.lang.basic.visual.misc


    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
     
    Poniente, Jun 26, 2011
    #5
  6. Poniente

    Guest

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

    GS Guest

    > i cant saveas with thesaveas prompt window keep showing i tried
    > everything please help


    Please show your code!

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 4, 2014
    #7
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. myoung

    DisplayAlerts property is not changing to False

    myoung, Nov 19, 2003, in forum: Excel Programming
    Replies:
    1
    Views:
    109
    Michael
    Nov 19, 2003
  2. Mark Driscol

    Re: Can't Set DisplayAlerts to False

    Mark Driscol, Apr 6, 2004, in forum: Excel Programming
    Replies:
    1
    Views:
    240
    Trevor Shuttleworth
    Apr 6, 2004
  3. Robin Hammond

    Tough One: displayalerts = false while OUTSIDE VBA

    Robin Hammond, May 31, 2004, in forum: Excel Programming
    Replies:
    2
    Views:
    71
    Robin Hammond
    May 31, 2004
  4. Jerry

    DisplayAlerts = False fails

    Jerry, Dec 8, 2004, in forum: Excel Programming
    Replies:
    1
    Views:
    63
    Jerry
    Dec 15, 2004
  5. Replies:
    3
    Views:
    254
    Jim Rech
    Mar 1, 2006
  6. Jim May

    Application.DisplayAlerts = False

    Jim May, Aug 13, 2006, in forum: Excel Programming
    Replies:
    10
    Views:
    186
    NickHK
    Aug 18, 2006
  7. Matt Simpson
    Replies:
    0
    Views:
    201
    Matt Simpson
    Aug 6, 2007
  8. Derek Dowle
    Replies:
    2
    Views:
    221
    Ryan H
    Dec 9, 2009
Loading...