Loading Capital IQ Plugin after starting excel with CreateObject

Question

I'm trying to automate a process in which I:

Open a file
Change some values based on another file
Execute a Macro
Close the file

The issue is that the Macro in question relies on the Capital IQ Plugin. The Macro works just fine when I open Excel myself, but errors out when I open excel via VBScript. I know from various google results that CreateObject doesn't load Addins properly. However, the results don't specify what to do when the Addin consists of an .xll file as well as a .dll file, and I'm not familiar enough with VB to deduce how to do it. When I open the list of installed Add-ins for Excel (after opening Excel myself), the relevant ones appear to be C:\Program Files\Capital IQ\Office Plug-in\CIQAddin.dll and C:\Program Files\Capital IQ\Office Plug-in\ciqfunctions.xll.

A popular solution online seems to be adding a function that toggles every addin

Function ReloadXLAddins(TheXLApp As Excel.Application) As Boolean

Dim CurrAddin As Excel.AddIn

For Each CurrAddin In TheXLApp.AddIns
    If CurrAddin.Installed Then
        CurrAddin.Installed = False
        CurrAddin.Installed = True
    End If
Next CurrAddin
End Function

But when I tried adding that, it still gave me an error.

Below is a sample of what my VBScript looks like.

sFolder = "C:\SomeFolderPath\"
sMacro = "C:\SomeFilePath.xlsm"
Set objFSO = CreateObject("Scripting.FileSystemObject")
For Each oFile in objFSO.GetFolder(sFolder).Files
    If UCase(objFSO.GetExtensionName(oFile.Name)) = "XLS" Then
        src_file = objFSO.GetAbsolutePathName(oFile)
        macro_file = objFSO.GetAbsolutePathName(sMacro)

        Dim oExcel
        Set oExcel = CreateObject("Excel.Application")
        Dim oBook
        Set oBook = oExcel.Workbooks.Open(src_file)

        Dim mBook
        Set mBook = oExcel.Workbooks.Open(macro_file)
        mBook.Sheets("input").Range("A1:A500").Value = oBook.Sheets(1).Range("A1:A500").Value
        oBook.Close False
        oExcel.Run mBook.name & "!Module1.myMacro"
        mBook.Close False
        oExcel.DisplayAlerts = False
        oExcel.Quit
        MsgBox("Done with file " & oFile.Name & "!")
    End if
Next

General comments on improving the performance / tidiness of the code are also appreciated.

Shffl Shffl · Accepted Answer · 2019-01-15T04:54:42

Posting the answer to my own question in case someone else encounters a similar challenge. Based on extensive testing, the issue is not that the Capital IQ Plugin isn't loading when Excel opens. This is despite Application.ComAddIns reporting that the S&P Capital IQ Plugin had a Connect attribute of True. The issue is that the COM AddIn for CapitalIQ takes roughly 20+ seconds to fully load (exact time varies). This caused issues when I call the "FindControl" Method of Application.CommandBars. I interpreted this as evidence that the plugin wasn't loading, when actually it was just taking longer to load. I noticed this when FindControl wouldn't work while I was Debug mode, but worked as soon as I exited Debug mode.

Part of the difficulty in diagnosing the issue is that FindControl has the odd behavior of throwing an error when no Control objects match the criteria, rather than returning None like the documentation suggests. Thus, I had to write a while loop to eventually detect the Control. At that point, the Macro would work like I wanted.

Finally, I found the win32com module for Python to be a better fit for my use case of automating Excel. This is unrelated to my original question, but is a best practice I picked up along the way.

Loading Capital IQ Plugin after starting excel with CreateObject

1 Answers