Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ActiveWorkbook.SaveAs needs autoresponse of Yes (Excel 2000 +)

    I am creating new spreadsheets dynamically from a master Excel workbook using an external program. Sometimes I need to password protect the document immediately. I am using "ActiveWorkbook.SaveAs Filename:=ThisWorkbook.FullName, FileFormat:=xlNormal, Password:=sPassword, WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False", but Excel always wants a response of "Yes" to overwrite the current workbook. I want the reponse to be automatically "Yes" because the user will be confused as to why this is being asked.

    I have tried SendKeys without success. Any ideas anyone?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveWorkbook.SaveAs needs autoresponse of Yes (Excel 2000 +)

    If you were doing this from a VBA macro within Excel, you could put:

    <pre> Application.DisplayAlerts = False
    </pre>


    before the SaveAs, and:

    <pre> Appliocation.DisplayAlerts = True
    </pre>


    after the SaveAs. However, since I don't know what this "external program" is or how it is working, I don't know if that will help.
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Oct 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveWorkbook.SaveAs needs autoresponse of Yes (Excel 2000 +)

    Thanks for the reply.

    There are three options available in the Save As dialog when overwriting the file, Yes, No and Cancel. Will it save anyway when DisplayAlerts is false? The external program is only copying a master workbook and the internal program in passing values to a macro to tell the program how to read a database for presentation... that's bascially all it's doing. Some of the data is sensitive so it needs a password. Since the master workbook has no password, I must ensure the copy of the master workbook (renamed of course) contains a password.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveWorkbook.SaveAs needs autoresponse of Yes (Excel 2000 +)

    In Excel VBA, the default for the Replace question is Yes, and that is what DisplayAlerts False forces.
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Oct 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveWorkbook.SaveAs needs autoresponse of Yes (Excel 2000 +)

    Thanks a lot!

Posting Permissions

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