Since most of the time there is a need to export acquired data to some sort of spreadsheet, it would be convenient to have a block of code that can be used over and over (this is the way a lot of programming is done anyway). One good example is exporting HP VEE data to Excel.
Figure 1 shows how a few simple blocks can be combined and used in any HP VEE program to export data to Excel. It is accompanied by an explanation of each block and how to implement them.
Figure 1
For this example, the For Count block is set at 50 which means this thread will repeat 50 times. With three rows for headings at the top of the spreadsheet, you will have room for only 50 more rows of data per column on an 8½” × 11″ sheet of paper. But what if you only want to take three measurements, or what if you want to take 3 million measurements? No problem.
All you do is create a prompt using an Integer Input Dialog box that asks how many measurements you would like to take. Then send this data to the For Count block. You will need to right-click on the For Count block and add a data input terminal to connect the output of the Integer Input block.
In some applications, you may write the code so that the program will automatically loop x number of times, and you won’t even need a prompt. Just send the output of each loop directly to the Collector. If you do this, set the initialization properties of the Collector accordingly.
The Random Number (labeled as Fake Data Generator in Figure 1) is used to generate some data for the purpose of programming. This is where you attach your program, maybe as a user object.
The block which accepts the data is the Collector. The Collector must be set to a one-dimensional array. This is done by clicking on the output shape button when the Collector is maximized.
An important part of the Collector is when to trigger it. In this case, the Collector is set to trigger after the For Count has finished. This is done by connecting the XEQ input of the Collector to the bottom of the For Count block. This way, you are sure the Collector is activated when the data from the Random Number (or your program data) arrives.
After collecting the data, send it to a To/From dynamic data exchange (DDE) block. This nice little gem exports the data to Excel. To format the way the data is imported into Excel, edit the properties of the To/From DDE.
If you right-click on the DDE block when it is minimized, it will bring up a menu. Once you are in the Edit Properties box, click on the Data Format tab. In the Separator Sequence portion of the box, change the End of Line (EOL) separator to “t” (quotes included). You also must change the array separator to “n”. Again, quotes must be included. This is how you tell Excel to format the data into columns. If you would rather export your data in rows, just reverse the procedure; that is, EOL = “n” and Array Separator = “t”.
Add a transaction and an input terminal. You can add the terminal by right-clicking on the To/From DDE for each column of data you wish to export to Excel. To add a transaction, either click in the next available space below the previous transaction or right-click and choose Add Transaction from the menu.
In the Transaction pop-up, select Write. You also need to define which cells to put the data into in the item field.
To properly display the data, the format would look something like this: “r3c1” (with quotes). This tells Excel to start putting the data from the Collector in columns starting at cell A3. Since the amount of data points is specified using the For Count, you don’t need to enter the starting and ending points of the array.
However, if you choose to use an open-ended data-collection scheme instead of limiting it to x amount of data points, you may want to specify the size of the array to use in each column. For instance, if you have 100 data points and want Excel to display them in a column using the open-ended method, enter “r3c1: r103c1” (with quotes) into the item field.
Finally, no data would be complete without headings and titles, so here is how to get them directly into Excel. Add a text box for every column (or row if you choose to display your data in a row rather than a column) of data. Enter the labels for each column/row into the text box.
Go back to the To/From DDE and add a transaction and input terminal for each label using the same method that was used for the data. In the item field, enter the cell address where you want the label to be located. Connect each label to the proper input and you’re done.
Conclusion
In programming, especially in Windows, there are many redundancies. As a result, there are many different ways to get your data to dance to the same beat. The method presented here has proven to work well. Hopefully, this will help demystify the process of importing data from HP VEE into Excel.
About the Author
Craig W. Clewell is a simulation engineer in the Engineering Advanced Products Group at Framatome Connectors USA. Previously, he was a test engineer at AMP. Mr. Clewell holds a B.S.E.E.T. from Penn State University and is a member of the IEEE EMC Society. Framatome Connectors USA, 825 Old Trail Rd., Etters, PA 17319, (717) 938-7486, e-mail: [email protected]
Return to PC-Based Test Online – Return to EE Home Page
Published by EE-Evaluation Engineering
All contents © 1999 Nelson Publishing Inc.
No reprint, distribution, or reuse in any medium is permitted
without the express written consent of the publisher.
June 1999