+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    G rumpy O ld D uffer
    Guest

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

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

  2. #2
    Rob Bovey
    Guest

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


    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" <[email protected]> wrote in message
    news:[email protected]...
    >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!!




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1