Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Surpress Error or other Excel genrated dialogs (2000/SR1)

    We are using a VB program to open an Excel template and then using Send Keys to open the VB editor, access the modules, etc. One problem we have run into is that upon first opring the Excel template using the Add method, there is a COM addin the template is trying to find and the COM addin is not there and we are getting a dialog stating that the Addin did not load and the dialog has an OK button. This is preventing our VB code from continuing. We have tried setting DisplayAlerts to False but this does not help. How should this be handled?

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Surpress Error or other Excel genrated dialogs (2000/SR1)

    Is this 100% of the time? Something you could just use SendKeys for?

    I've read here about some little programs that you can use to monitor for and dismiss dialogs. I didn't keep all the names, but I do remember one called "ClickYes" and I bookmarked Buzof from Basta Computing. Haven't tested either.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Surpress Error or other Excel genrated dialogs (2000/SR1)

    Just some questions if I may.

    This COM addin is explicitly tied to this particular template?
    Or does the com add-in load on loading of Excel itself?

    May I ask why you are using sendkeys to access the VBE and modules?
    This opens the VBE window:

    Application.VBE.MainWindow.Visible = True
    Replace Application with the object variable that holds your XL session:

    The Office VBE extensibilty library provides lots of stuff that enables you to programmatically access code in workbooks.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Surpress Error or other Excel genrated dialogs (2000/SR1)

    We have hundreds of custom Excel templates and much of the code is reusable but due to requirements we are not able to use a COM addin or XLA type addin so we are looking for a better way to update the templates when the update involves a VBA module such as a bas, frm, or cls file.

    The reason we are using sendkeys is because the VBA properties has a password on it so we need to send the password so as to be able to access the modules.

    Yes we have been using VBE object but much is difficult to understand how to use in the way we wish.

    Thanks

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Surpress Error or other Excel genrated dialogs (2000/SR1)

    But you mentioned a com addin popup that needs to be OK'ed. My question was about that COM add-in: how does it get loaded, at start of XL or at load of a particular workbook/template.

    I understand te sendkeys part with respect to unprotecting the project(s), I see no way around that.

    After that however, sendkeys should and can be avoided.

    Exactly what are you trying to accomplish after unprotecting?

    - Deleting a module/form or class module?
    - importing a module from file?

    Both tasks aren't too difficult.

    Deleting a vbcomponent called "Test"::

    Application.VBE.ActiveVBProject.VBComponents.Remov e Application.VBE.ActiveVBProject.VBComponents("Test ")

    Importing a file:

    Application.VBE.ActiveVBProject.VBComponents.Impor t("test.bas")
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Surpress Error or other Excel genrated dialogs (2000/SR1)

    I am sorry, the COM addin is loaded automatically.

    Thanks for the other information and yes the send keys will only be used for the password.

    We will be updating a module by first removing the old one and then inporting the new one.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Surpress Error or other Excel genrated dialogs (2000/SR1)

    <hr>I am sorry, the COM addin is loaded automatically.<hr>
    Yes, but is it loaded by Excel at startup of Excel or during the loading of the template?

    If the latter, try disabling events before the Open command :

    Application.EnableEvents=False
    Workbooks.Open "c:dataTheTempateToOpen.xls"
    Application.EnableEvents=True
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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