08-19-2014 05:11 AM
I am trying to create a macro within excel to import and format data from a .tdm file.
The files to be imported sometimes exceed the 1M row limit in Excel, so when this happens, I want to generate two workbooks. One of these workbooks should be generated from a start index of 1 and the other should be generated from a start index of 1048576. Right now I use the silent mode to give a start index of 1 and then rerun the importer and enter 1048576 manually for the second workbook.
My question is: is there any way to set the start index through a macro?
I have considered using the sendkey command to generate the "tab tab 1048576 enter" keystrokes required, but excel cannot interrupt the call object command in order to do this.
Solved! Go to Solution.
08-20-2014 03:38 AM
What software are you using ActiveX in ?
In LabVIEW you can use ActiveX to format the data and create workbooks.
Have you considered using DIAdem which is designed to work on large data sets ?
08-20-2014 03:48 AM
There is no ActiveX here, just a VBA macro running the Excel TDM importer add in (Application.COMAddIns.Item("ExcelTDM.TDMAddin"))
I have looked into DIAdem, which I currently use for some data analysis, but could not find a way to perform some of the calculations I'm using in excel. As far as i can tell, if calculating the nth value for a new channel, you can only look at the nth value for other channels. An example of one of the channels I'm creating in excel is:
Is there any way to do this in DIAdem? If so, this would probably make my life a lot easier...
08-20-2014 04:56 AM
I found that the FIND function may be of use to you.
http://digital.ni.com/public.nsf/allkb/FD7EB36833694FB986256B99005A6394 - Way to find specific values in DIAdem-Data channel using the FIND function.
You can use Visual Basic script for DIAdem http://www.ni.com/diadem/whatis/automatedanalysis/
There is also a feature in DIAdem where it records your analysis and then creates the script for you.
http://www.ni.com/white-paper/13974/en/ - Here is a white paper that demonstrates this.
Let me know if this helps
08-20-2014 05:17 AM - edited 08-20-2014 05:18 AM
Thank you for your help. Unfortunately the FIND function isn't quite what I'm looking for. I will try to give you a bit more information about the task I'm trying to accomplish. Below is an outline of each of the additional channels I'm trying to create. I will call my source channels containing timestamps and data "time" and "data" for simplicity. I wish to generate values in these channels for every timestamp and use the nth term in my formulae.
Moving average: A 75 point moving average centred around the corresponding time. Moving average (n) = IFERROR(AVERAGE(Data(n-37):Data(n+37)),0)
I'm happy that the Moving average can be easily accomplished in DIAdem
Variation: The difference between the maximum and minimum values over 75 points centred around the corresponding time divided by twice the moving average at that time. There are also addtional criteria, which if not met should return a 0. If the difference between the moving average 37 points previously and the moving average 37 points ahead is more than half the moving average, return 0. If the moving average for that datapoint is less than 30, return 0. If the timestamp is not between two predefined points, return 0.
The formula for this is: VARIATION = IF(AND(time(n)>MINTIME, time(n)<MAXTIME, Moving average(n)>30,ABS(Moving average(n+37)-Moving average CC RPM(n-37))<Moving average(n)/2),(MAX(DATA(n-37):CC_RPM(n+37))-MIN(DATA(n-37):DATA(n+37)))/(Moving average(n)*2),0)*100
I'm not sure this can be accomplished in DIAdem, which is why I'm currently using Excel. If you know how this can be done I would be very grateful!
Acceleration: Difference in DATA divided by difference in TIME with the same conditions as above.
The formula for this is: ACCELERATION = IF(AND(time(n)>MINTIME, time(n)<MAXTIME, Moving average(n)>30,ABS(Moving average(n+37)-Moving average(n-37))<Moving average(n)/2), ABS((Data(n)-Data(n-1))/((time(n)-Time(n-1))*24*60*60))
Again, I'm not sure this can be done in DIAdem.
Sorry this is all a bit complex!
Edit: The 😄 smilies are actually meant to be a : followed by a D
08-20-2014 08:52 AM
I've looked at the best way to use formulas in DIAdem and I think calculation manager will aid you. Here is a white paper explained the concept ( http://zone.ni.com/reference/en-XX/help/370858K-01/concepts/diadem_4_07/ ).
I also found a free DIAdem hands on document that I think will be helpful ( http://zone.ni.com/devzone/cda/epd/p/id/5393 ) . In particular look at exercise 6, step 30 of diadem version 11.1 .
Let me know if this helps.
08-20-2014 10:03 AM
I asked R&D, and they said there is currenlty no way (in 2014) of passing the desired start index to the TDM Excel Addin. They have added that idea to their feature request list, so it may show up in the future.
DIAdem Product Support Engineer
08-21-2014 06:25 AM
I have done quite complex processing with DIAdem. Your request is not a big deal to do in DIAdem.
For max flexibilty, you can take out references to the channel. Like shown below in oChan01. This will allow you to handle a channel like an array. The limitations on this approach is speed, The built in functions are faster than this approach, and should be used as much as possible. The chnfind command is one examle that is much faster to find a specific value in a channel.
OptionExplicit'Forces the explicit declaration of all the variables in a script.
set oGrp = data.Root.ChannelGroups("EXAMPLE")
set oChan01 = oGrp.Channels("Time")
for i = 1to oChan01.Size
dVal = oChan01(2)
08-21-2014 07:07 AM
Thanks to everyone for the responses.
The calculation manager is very useful for a lot of things. I've used it a fair bit but unfortunately it can't refer to channels as arrays. I didn't see a version 11.1 link in the hands on document. I looked at 6.30 step for the DIAdem 2014 but it didn't really seem to be applicable to what I'm trying to do here.
Thanks for forwarding this request. Hopefully it shouldn't be too tricky for them to implement. I would expect this to be quite a useful function, though the fact that nobody has asked for it before now may suggest otherwise!
Thanks a lot! This is exactly the kind of thing I'm looking for. You're correct that speed of calculation could well be an issue, but I wouldn't expect it to be significantly worse than excel.
08-21-2014 12:55 PM
We recently pulled the 11.1 version of the DIAdem Hands-On document from that web site. Doh! I just posted it to the following ftp site, where it will be available for the next few days.
"ftp.ni.com/outgoing/DIAdem 11.1 Hands-On.doc"
DIAdem Product Support Engineer