Automating Excel TDM Plugin

[Julia Dawkins]

Well, I am a little embarrased to ask this, since I am not an Excel fan, but I know people who are!  I need a way to nicely export data to Excel files and re-open them.  I have written scripts that utilize the ExcelExport function, but I much prefer saving TDM/TDX files because (1) they seem MUCH smaller (8 MB TDM/TDX file versus 30 MB Excel - is this normal?) and (2) I don't have to worry about saving more than 256 columns or 65536 rows of data, so then this is the user's problem for using Excel rather than DIAdem!  Anyway, so I like to save TDM files and then have users use the "Import TDM file" in Excel, but I was wondering if I could call this somehow within a DIAdem script?  I can write a script in DIAdem to open Excel or an Excel file, but haven't been able to figure out how to open a TDM file in Excel using DIAdem.  I appreciate your help.

Julia Moeller

[Sabcat]

Hello Julia,

Perhaps the 'TDM Add-In for Microsoft Excel' is something for you...?

http://zone.ni.com/devzone/cda/epd/p/id/2944

[Julia Dawkins]

Thank you, I am actually already using the TDM Add-In for Excel, but would like to control it within DIAdem, if that makes sense. Rather than having to open Excel and push the button on the new toolbar, I would prefer to write a script in DIAdem to open Excel and do this for me.  I'm not sure if this is possible though.

[Twigeater]

Hello Julia!

In general this should be no problem. It is unfortunate that this does not apply to the NI Excel TDM AddIn

The AddIns can be accessed via the ComAddIns collection. After connecting you can get a object, wich should expose a scriptable interface to the AddIn features. In this case it should be at least a Load method with the filename as parameter.

The following script shows how to find the right AddIn and get the object interface (or nothing if not implemented). You can run it in DIAdem:

Option Explicit
 
Dim oExcel
Dim oComAddIn
 
Set oExcel = CreateObject("Excel.Application")
 
For Each oComAddIn In oExcel.COMAddIns
 If oComAddIn.ProgId = "ExcelTDM.TDMAddin" Then
    oComAddIn.Connect = True
 
    If Not oComAddIn.Object Is Nothing Then
      ' use the object interface here
    Else
      MsgBox "No object interface available! :-((("
   End If
  End If
Next
 

Matthias

[AEDavid]

Hi Julia,

I have also attached an example that opens up Excel from a script.  Note that Excel pops up when Excel.Visible is called. 

[Twigeater]

Hello David!

Where is the TDM AddIn automation in your example?

I only can find a data copy cell by cell. A verry slow aproach if you have a lot of data! You can do it much faster with some hidden features of Excel, but if you have verry much data the AddIn is still faster.

Matthias

[Julia Dawkins]

Hi David,

Yes, as Matthias said, my data becomes very large (and slow) if I save it in XLS.  And it is possible that some of my data will exceed Excel's limits, so would prefer to not have to handle that.  If I save it in TDM/TDX format, it is MUCH smaller and then I can also index this data via the DataFinder!  Super.  Except that I have to open Excel manually to get this file 😞

Julia

[jbrandim]

This will open Excel and run the 'TDM Importer'.  Run this code in Diadem...

Dim oExcel
Dim oComAddIn
Dim objTdmImportCtrl

Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
Set objTdmImportCtrl = oExcel.CommandBars("TDM Importer").Controls.Item(1)
Call WndShow("SHELL", "MINIMIZE")
objTdmImportCtrl.Execute
Set oExcel = Nothing

I haven't been able to avoid using the Importer's dialog to specify the file to import.
I wish there was a way, as this would be very helpful to me.

[shb]

Hello jbrandim

You could enter the filename including the path by simulating keystrokes. (If Sendkeys does not exist in Diadem, use oExcel.SendKeys) Do not forget to send enter in the end to close the dialog. To close the error messages which may appear I send escape twice.

The problem is that execute does not return until the dialog is closed. Maybe the keys can be sent right before the call. If the script language supports multithreading, you can send the keys from a second thread.

A third approach is to replace the call objTdmImportCtrl.Execute by objTdmImportCtrl.Activate and send enter after. The dialog opens and the script continues. It can send the path now.

I use the second approach in a LabVIEW programm. It is not a nice solution, but it works.

An Excel macro which uses the third solution contains the following lines:

    CommandBars("TDM Importer").Controls(1).SetFocus
    SendKeys "{ENTER}" & tdmPath & "{ENTER}{ESC}{ESC}"

Greetings

shb

[ThomAC]

There is a new version of the TDM Add-in for Microsoft Excel available which now includes a COM-API. This API allows you to automate or remotely control the TDM import.

Please find the new Add-in at:

http://zone.ni.com/devzone/cda/tut/p/id/4906

A description of the COM interface can be found at:

http://zone.ni.com/devzone/cda/tut/p/id/10207

Greetings,

ThomAC