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!!

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

"G rumpy O ld D uffer" wrote in message
om...
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!!