Excel sheet modification in a program.

[ryosaeba]

I have modified Agilent 34970 Advanced Scan with while loop, time dealy and shift register. I have also included in this while loop the write table to excel.Vi. This gives an excel sheet after each run with time in the first column, voltage in the second column and and channel in the third column. I would like to get time in hte first colum, channel1 in the second column, channel 2 in the third column. and want the to be written in one excel sheet row after row. how do i go about it? i am using labview 8.2.1.

[smercurio_fc · Accepted solution]

You basically have to rearrange your columns before you write. Are you having an issue with how to do this?

Other comments:

• Your use of local variables is a race condition. Do not use the "Table" local variable. Use a wire instead. See here.
• Do not initialize the VISA session inside the loop. Initialize it once outside the loop, and close it when the loop is done.
• Do not open Excel each time you run around the loop. Open it once before you start the loop, and close it when the loop is done.
• What's with all the comments about how to create objects like local variables and property nodes? Proper commenting would be to explain algorithms, not how to, for example, write an "if" statement. 

[Andrew McL]

Hi ryosaeba,

I've attached a VI that I wrote a while back which uses ActiveX to control Excel and writes to specific cells on a spreadsheet. I believe you should be able to use this as a basis for your application.

Please let me know if you have any questions.

Many thanks,

[ryosaeba]

HI Thanks for your comment. I am only beginning to program in lab view. I have  those comments to remid me how i have done it otherwise I will forget it.

[ryosaeba]

Hi smercurio,

yes I have issue with rearranging  the columns before i write. How could I do this?

thanks

[smercurio_fc]

I don't have a 34970 so I can't run your code so I don't know what your data looks like. You said that the third column contains channel, and you want the data written to file to have channel 1 in the second column and channel 2 in the third column. Does this mean that the table control has values like "1", and "2" to indicate channel number?

One other comment: Do you absolutely have to use an Excel workbook? You may want to consider using the Write to Spreadsheet File VI. This will create a delimited text file that can be opened with Excel. This will be much faster than the ActiveX method which is required with Excel (though you can also use ADO, but that's a different story).

[ryosaeba]

Hi smercurio,

This is what I get with trial13.vi

time	Voltage	Channel	Sam No. & Po
12/05/2009 00:00	-2.68404570E-01 VDC	101
12/05/2009     02.00	+9.87424510E-01 VDC	102

 This is what i want

time	channel 101	channel 102	channel 103
12/05/2009 00:00	-2.68404570E-01 VDC	+9.87424510E-01 VDC
12/05/2009 02:00	-2.68404570E-01 VDC	+9.87424510E-01 VDC

[ryosaeba]

Hi Andrew,

Thanks for your reply. I will try to once again. if I have any questions I will let you know.

[ryosaeba]

Hi smercurio,

I don't need Excel work book. All I need is one single spread sheet where I will be able to write line after line after each iteration of the program.

What i expect to get is the following

time	channel 101	channel 102	channel 103
12/05/2009 00:00	-2.68404570E-01 VDC	+9.87424510E-01 VDC
12/05/2009 02:00	-2.68404570E-01 VDC	+9.87424510E-01 VDC

thank you

[smercurio_fc]

Since you are specifying the channel range on the front panel you can use this to create column headers directly. Instead of using row headers I would make time as an additional column to the table. This makes the writing to file much simpler. 

There are several ways to parse out the string you read from the device. Based on what I see, it seems the string read from the device is voltage, channel, voltage, channel, ... Given this, I've attached a VI showing you 2 ways you can parse this string to get your table organized with channels as columns. The alternate approach that is shown in the VI may be more desirable since it has a Trim Whitespace VI in it (though you can add it to method 1). 

As for writing to file, you can simply wire the 2D array that's being fed to the table indicator directly to the Write to Spreadsheet File VI.