Automatically Close Dialog Boxes
 

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.


--
chriskane
------------------------------------------------------------------------
chriskane's Profile: http://www.excelforum.com/member.php...o&userid=26068
View this thread: http://www.excelforum.com/showthread...hreadid=394037

The next dialog box to appear asks the user whether to allow this
workbook to pull in values from another workbook
..
This can be overcome by Tools Options Edit Uncheck 'Ask to
automaticaly update links'

Not sure about the last one!

Sorry,
Regards,
Alan.
"chriskane" wrote
in message ...

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.


--
chriskane
------------------------------------------------------------------------
chriskane's Profile:
http://www.excelforum.com/member.php...o&userid=26068
View this thread: http://www.excelforum.com/showthread...hreadid=394037

Not sure if this will work but try

Application.DisplayAlerts = False
'Your code here
Application.DisplayAlerts = True

Regards,
Alan.
"Alan" wrote in message
...
The next dialog box to appear asks the user whether to allow this
workbook to pull in values from another workbook
.
This can be overcome by Tools Options Edit Uncheck 'Ask to
automaticaly update links'

Not sure about the last one!

Sorry,
Regards,
Alan.
"chriskane" wrote
in message ...

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.


--
chriskane
------------------------------------------------------------------------
chriskane's Profile:
http://www.excelforum.com/member.php...o&userid=26068
View this thread:
http://www.excelforum.com/showthread...hreadid=394037