Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    Ontario, Canada
    Posts
    57
    Thanks
    1
    Thanked 0 Times in 0 Posts

    auto reply to an excel message (2003)

    Edited by HansV to replace Word document with the screenshot it contained - saves the reader a few clicks.

    I have a macro which opens a number of csv files and then copies them as sheets in a pre-existing workbook. There is a named range in the pre-existing workbook, and a reference to that range in the csv files.

    When I run the macro I get the messagebox shown in the attachment popping up, and I have to click on the yes button to continue.

    How can I automate this so that I don't have to click on the button?

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

    Re: auto reply to an excel message (2003)

    Insert a line

    Application.DisplayAlerts = False

    before the offending code, and

    Application.DisplayAlerts = True

    after it.

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    Ontario, Canada
    Posts
    57
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: auto reply to an excel message (2003)

    Thanks Hans

    That will presumably "accept" the default reply on the message box. What if I wanted to do the "no" instead?

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

    Re: auto reply to an excel message (2003)

    Setting DisplayAlerts to False will indeed make Excel act as if you clicked the default button. If you want to click the "No" button, it is trickier. You could try sending the "N" key:

    SendKeys "N"

    before the line that displays the dialog.

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Location
    Ontario, Canada
    Posts
    57
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: auto reply to an excel message (2003)

    Thanks Hans.

Posting Permissions

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