Code to select 'Disable Macro' when opening an EXTERNAL FILE

[G rumpy O ld D uffer]

I have some coding in an 'Archive' file that opens an external file
(Test Template) to obtain a line of data, which is then copied into
the 'Archive' file, and then closes the external file.

Because of some coding in the external file I cannot copy the line of
data as Cut, Copy & Paste automatically 'switch off' when the external
file is opened.

What coding do I need so that when the external file is opened, in
EXCEL 2000, and the dialogue box appears asking to 'Disable
Macro'/'Enable Macro'/'More Info', 'Disable Macro' is selected ?

This is the coding I have so far in the 'Archive' file to open the
external file, navigate to the correct worksheet, then to the named
range to be copied :
Workbooks.Open Filename:="C:\Test\Test Template.xls"
Windows("Test Template.xls").Activate
Sheets("DataBase Line").Select
Range("To_Database").Select
Selection.Copy

The last line of the coding does not work, because I haven't been able
to work out the necessary coding to 'Disable Macro' when the file is
opened. I'm guessing I need something added to the first line of the
above code!!

I know all about the dangers of disabling the Dialogue Box and how to
do it through Tools/Macro/Security, but as it's an external file and
all I want is to grab some data from the External file and copy into
an 'Archive' file, then close the External file, there must be some
way of doing this!!

 

[Rob Bovey]

Turn off Excel event handling at the beginning of your code and then
turn it back off at the end:

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

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm