Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jun 2008
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Form 2nd WB (O2000SR1)

    My program imports data from one workbook into another. I use xldialogsopen to pick the file to import from. This all works except that I have an on-open macro that fires off in the import file. Is there a way to open the import WB without triggering on-open macros?

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

    Re: Importing Form 2nd WB (O2000SR1)

    Are you positive that the Auto-Open macro is executing. I didn't think that it did, and this is what Help for the Open Method says in the last line in my XL 2000:

    "If the workbook being opened has any Auto_Open macros in it, they won
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Aug 2001
    Location
    Shropshire, UK
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Form 2nd WB (O2000SR1)

    The answer might be because of the use of a built in dialog box. As far as Excel is concerned this is not using VBA. Try assigning the selected file path and name to a variable then using the Workbooks.Open method. Ofcourse you shouldn't execute the Open file dialog box.
    Ewan

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Form 2nd WB (O2000SR1)

    Are you talking about the Workbooks.Open method, or the xlDialogFileOpen dialog? If you are having a problem using a variable to pass the filename to the Workbooks.Open method, I have never had any problem doing that. Show us the code that you are having a problem with and tell us what problem you are having and we will try to help. If you are trying to get the xlDialogFileOpen dialog box to return the name of the file opened, it does not do that. However, after the file is opened, you can use the Name, Path, and FullName properties to get the name of the file that was opened. Is that what you are trying to do?
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Jun 2008
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Form 2nd WB (O2000SR1)

    This is the code I use to open the old workbook (with data to be imported into the new workbook):
    Application.Dialogs(xlDialogOpen).Show
    This pops a nice dialog, and I can proceed with no trouble. My only difficulty is that I have a Workbook_Open routine in the old workbook, and it is triggered when that workbook is opened. The funny thing is that this seems to be a recent problem because I don't remember having this problem in the past with Office 97. The Workbook_Open routine displays another form with some password sign on options and so forth, so it is confusing to someone trying to import data from an old version. I want to open the old workbook using the dialog, but avoid triggering any macros from the old workbook.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Importing Form 2nd WB (O2000SR1)

    As far as I know the only way around disabling a Workbbok_Open macro (apart from Disabling macros, which cannot be done in VBA) is to hold down the Shift key whilst you selecting the file to open. If however you can change your Workbook_Open to Auto_Open in a general module, then using the Workbooks.Open method as follows would open without the macro running : <pre> strFileName = Application.GetOpenFilename
    If strFileName <> False Then
    Workbooks.Open strFileName
    End If
    </pre>

    Andrew C

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Form 2nd WB (O2000SR1)

    Andrew: I have just discovered that the Open Event does trigger when the Workbook is opened from VBA code, even though the Help file seems to indicate that it will not. However, it also looks like Application.EnableEvents=False before the open will keep it from running.
    Legare Coleman

  8. #8
    New Lounger
    Join Date
    Jun 2008
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Form 2nd WB (O2000SR1)

    Andrew, I tested your solution and it worked beautifully. However, it dawned on me that I have multiple copies of the application already in the field, so I cannot change the way the open macros work in those copies. I will now try a combination of what you suggested and the disabling of triggered events suggested below.

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Importing Form 2nd WB (O2000SR1)

    As Legare has reminded me of EnableEvents, I think your solution is :<pre> Sub ImportFile()
    AppEvents = Application.EnableEvents
    If AppEvents Then Application.EnableEvents = False
    result = Application.Dialogs(xlDialogOpen).Show
    Application.EnableEvents = AppEvents
    End Sub
    </pre>

    Or<pre> Sub ImportFile()
    AppEvents = Application.EnableEvents
    If AppEvents Then Application.EnableEvents = False
    strFileName = Application.GetOpenFilename
    If strFileName <> False Then
    Workbooks.Open strFileName
    End If
    Application.EnableEvents = AppEvents
    End Sub
    </pre>

    Try either of the above and it should work for you.

    Andrew

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Importing Form 2nd WB (O2000SR1)

    Legare thanks for that. Actually the Auto_Open does not seem to trigger when opening by use of the Workbooks.Open method, but ThisWorkbook events are not bypassed. Unless events are disabled as you rightly pointed out.

    Andrew

  11. #11
    New Lounger
    Join Date
    Jun 2008
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Form 2nd WB (O2000SR1)

    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    Thanks Legare and Andrew. I leaned about how to display the file dialog without opening the file (Application.GetOpenFileName), how to turn on and off the events, and finally, the small error in the help.
    It works like a champ.

Posting Permissions

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