Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Close with no save or prompt

    I have a patchwork "program" assembled to automate report harvesting from our AS/400 to my PC. Starting with Client Access, I dump a listing of the outqueue to a text file and read this file into Monarch. Monarch converts the text file into a data table, applies some filters, and exports an abbreviated listing into an Excel spreadsheet. A second Excel spreadsheet links to the first and inserts the data into a pre-defined pattern in a single column, which is then saved as a text file. This text file is fed back into Client Access and executed as a macro to harvest the selected reports from the outqueue.

    The whole process runs almost completely hands-free, except for the Excel spreadsheet that creates the new text file. On opening, a macro performs a "SaveAs" and "Exit", but at this point in the processing Excel pops up annoying prompts that prevent the whole "program" from operating hands-off.

    I would like to have this spreadsheet open, process the links, save as a text file, and close itself automatically, without interruption. Is this possible? Let me tell Excel that it is ok to over-write the existing file, and quit without any warning notice about losing features and such.

    (I set this patchwork up some time ago, and I'm sure that I could work most of it out to run smoother, but performing a little fix on one part would be much faster than reworking the whole process.)

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Close with no save or prompt

    Switch off Excel displayalerts, using

    Application.DisplayAlerts = False

    somewhere in the beginning of your program, and switch them back on somewhere at the end (immediately before exit sub or end sub)

    Application.DisplayAlerts = True

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

    Re: Close with no save or prompt

    I agree with the advice that Hans gave you with one minor exception. I would put those two statements immediately before and after the statement causing the message that you do not want (the Workbook.Saveas in your case). That way, you will not miss any unexpected errors that might cause you other problems.
    Legare Coleman

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Close with no save or prompt

    I absolutely agree with you Legare, that's indeed Good Programming Practice. My post - and it was based on that assumption - is only true if you are sure that no errors might occur.

Posting Permissions

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