OLE Automation: Add-Ins Don't Load When Using CreateObject

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

When you access Microsoft Excel as an OLE Automation object using the CreateObject command, add-ins, files located in the XLStart directory, and the default new workbook are not loaded.

CAUSE

Add-ins, files in the XLStart directory, and the default new workbook are not loaded when Microsoft Excel is run as an OLE Automation object using the CreateObject command. This is by design. When add-ins and files are loaded, Microsoft Excel sends a message to the calling application, such as Visual Basic version 3.0, that it is not ready to respond to the request and to try again later. The calling application may not be able to handle this request, and will not be able to continue with the rest of the commands.

WORKAROUND

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

To load an add-in file when you run Microsoft Excel as an OLE Automation object, load the add-in manually. The following is an example of using this method:

   Sub LoadAddin()

      ' Dimension variable xl as object type
      Dim xl As Object

      ' Activate Microsoft Excel and assign to variable xl
      Set XL = CreateObject("Excel.Application")

      ' Open the desired add-in file, in this example, XLQUERY.XLA
      XL.Workbooks.Open (XL.librarypath & "\MSQUERY\XLQUERY.XLA")

      ' Run any auto macros contained in the add-in file
      ' Auto macros don't run when you open a file
      ' using the Open method
      XL.Workbooks("xlquery.xla").RunAutoMacros 1

      Set XL = Nothing

   End Sub

MORE INFORMATION

Because Microsoft Excel does not load add-ins or files in the XLStart directory when you call it as an OLE Automation object, you have complete control of the loading process. If you want an add-in loaded, you can load it manually. This behavior also means that no macro errors, locked file alerts, or read-only file alerts will keep Microsoft Excel from loading when it is being called by the CreateObject command. Another benefit of this behavior is that it takes less time to load Microsoft Excel than it does when add-ins or files are loaded when you run Microsoft Excel.

REFERENCES

For more information about an Overview of Controlling What Happens When you start Microsoft Excel, choose the Search button in Help and type:

   startup workbook

    add-ins