Error with TDM add-in using COM automation ImportFile

[FTI_Newton]

The importer works fine within Excel. However, when using automation from within LabVIEW I keep getting error "-2147417851" on the output of method "ImportFile".
Code snippet below.

The PC I'm using has...

OS = Windows 7 

Excel 2010, all latest service packs and Office updates.

LabVIEW 2009

TDM for Excel Add-In v.3.5.1

The exact same calls work on another computer with the following:

OS = Windows XP

Excel 2007, all latest service packs and Office updates.

LabVIEW 2009

TDM for Excel Add-In v.3.5.1

Can't seem to find any docs or reason for it not to work. The libraries show up as registered from the dropdowns. The Importer works from within Excel. I've rebooted updated and reinstalled and rebooted what feels like a million times already. What am I missing here? Any ideas?

[FTI_Newton]

PS - ignore the "Quit Application" race condition - I have a Disable Diagram structure around it right now - I took it out to make the snippet less confusing and now it's more confusing. doh! 

[FTI_Newton]

BTW, I'm sure this is related... making a call to "Show Config Dialog" does nothing, and doesn't return an error either.

On the other PC (the one that works) it pops up the dialog as expected.

[FTI_Newton]

I installed Office 2010 on the other computer (the one that works), and the TDM add-in still works. Dang. Starting to smell Windows 7 related?

[Matt_L]

Hi FTI_Newton,

In some of the related issues to this that I've seen the issue was due to some 64 bit/ 32 bit incompatibilites.  Are you running Windows 7 32 or 64 bit, and are you using the 32 bit version of LabVIEW?  Another thing you might try is running LabVIEW with adminstrator privileges, and also in compatibility mode for Windows XP SP3.

[Mavis · Accepted solution]

I remember we should Connect/Disconnect the Addin to/from Excel before/after using it.

[FTI_Newton]

Mavis: Thank you not only for a meaningful reply , but the actual solution!

A small detail: I found that I could not disconnect right away or I'd get another error - waiting to disconnect until after I had a chance to save the file ensured that it was fully loaded.

Is this documented somewhere? The VBA code example on this page http://www.ni.com/white-paper/10207/en/rating/2  shows the call to connect, but there doestn' see m to be any doc on the method.

Also, this solution begs the question: how did the code with the earlier version of the add-in work correctly without specifically calling 'connect' first? I'm sure someone will say it doesnt' matter, but that's what threw me for a loop all along;  it might very well matter to the next person that runs into the same problem. Worked in Case A, how is Case B different?

Thanks again, Mav.

Can I call you Mav? 

[Mavis]

Hello FTI_Newton,

I think the VBA example is for the case that we get the Add-in object from Excel.

Set obj = Application.COMAddIns.Item("ExcelTDM.TDMAddin")

obj.Connect = True

Connect is an attribute here. By default, the value is true, so we don't necessarily need this line. If set it to False, we will get an error.

Back to your VI, it's getting the Add-in object independent of Excel, so we have to pass Excel to it.

Set oComAddIn = CreateObject("ExcelTDM.TDMAddin")

oComAddIn.Connect(oExcel)

I tried an earlier Add-in version 3.3.0 on a clean XP (Excel 2010) without Connect and got an error. I'm also thinking how it could ever work without Connect. 

[FTI_Newton]

I'm not sure. I tried a couple things and couldn't replicate after the reinstall, and I haven't had time (or inclination to be honest) to make a full test matrix.

I suppose there may have been some combination of having Excel open and having run the add-in that wound up holding it open. Mysterious!