Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Avoiding Save Dialogs (Excel 2002)

    I've been trying to tidy up a routine that saves a workbook in Excel 3 Format (this is the only Excel format that I find uploads and downloads from an AS/400 without any problems). I wanted to avoid the 'Do you want to save in the latest format?' dialog so that the user can't say 'Yes' to this, so I used Save As, which has a format parameter.
    But of course I then get a 'Do you want to overwrite the existing file?' dialog, to which the user can answer 'No'.
    I'm sure this is an easy one - can someone please enlighten me as to how to save a file forcing both the save format and the overwrite without any dialogs?

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

    Re: Avoiding Save Dialogs (Excel 2002)

    Insert an instruction

    Application.DisplayAlerts = False

    before the SaveAs, and

    Application.DisplayAlerts = True

    after it. Setting DisplayAlerts to False will suppress dialogs. You must make sure that DisplayAlerts is set to True again, otherwise you will get unexpected results. It is a good idea to place it in a section of the code that will always be executed, even if an error occurs:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Sub SaveAsExcel3()
    On Error Goto Err_Handler

    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "C:ExcelTest.xls", xlExcel3

    Exit_Handler:
    Application.DisplayAlerts = True
    Exit Sub

    Err_Handler:
    MsgBox Err.Description, vbExclamation
    Resume Exit_Handler
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

  3. #3
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding Save Dialogs (Excel 2002)

    Thanks, Hans - very helpful!

    Mike

Posting Permissions

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