Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Aug 2005
    Thanked 0 Times in 0 Posts

    Automatically Close Dialog Boxes (Excel 2000, VB 6)

    I am writing an excel macro (first time doing this) which pulls in company credit ratings from a Bloomberg terminal, compares the credit ratings with ratings in a database, and writes any updated credit ratings into the database. The code works fine, but now I want to have the sequence run once a week (say, every Monday morning). This seems easy enough to do by setting a windows event that will open the excel workbook every Monday morning and then setting the macro sequence to execute when the workbook opens. Then I would just add in a line telling the workbook to save itself and close excel once the sequence finishes.

    The problem is that several dialog boxes appear when the workbook is open. The first asks the user whether to enable macros. I've gotten around this by setting low security so that macros are always enabled. The next dialog box to appear asks the user whether to allow this workbook to pull in values from another workbook. Then there is another dialog box that asks the user to approve opening an external program that pulls in data from Bloomberg.

    I need to find some way to automatically approve these dialog boxes. I've tried playing with the SendKeys command, but with no luck. I'm happy to send the file or just the code.

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

    Re: Automatically Close Dialog Boxes (Excel 2000, VB 6)

    Your first issue is the security dialog boxes to enable macros. The virus writers would love it if you could do what you are asking. So, the direct answer to that question is that you can not automatically respond to this dialog, it is displayed before any code in the workbook is run. You basically have two ways around this problem. One is to do the highly NOT recommended action of changing your security level to low. If you do this, you are opening yourself to the each infiltration of viruses.

    Your second alternative is to electronically sign the macros and then tell windows security that you trust code that has been signed with this electronic signature. You can either buy an electronic signature from one of the companies that sell this service, or you can use the SelfCert program that comes with MS Office to create your own signature. Its been more than 5 years since I have done this, so I don't remember the exact procedure.

    Since I don't know what the external program is, or which program is creating the second dialog box, I can't say how to handle that one. You might be able to use automation to comunicate with the external program, or you might be able to use SendKeys, or it might take something else. It may not even be possible. You are most likely going to have to get that information from the creator of that program.
    Legare Coleman

Posting Permissions

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