Need simple example for writing to Excel

[Joe_H]

Ok here is what you do.

1.To create an excel refnum: on the controls pallette find refnums. Place an automation open refnum on the front panel.

2. Right click on the automation refnum, then select Active X class then select browse.

3. Find microsoft excel object library. this will give you all the objects for excel. use these refnums with the excel spreadsheet you want to write. Not the file refnums you are writing to.

when you are able to do this please post back with the vi you created. Do this with a new vi so we can complete each step together.

[spaceman_spif]

I'm still working on the code you suggested.  But in the meantime, a question.

If you look at my code sample "Secret origins...", you see where I create the file.  Originally, it was a text file (the extension going into the concatenate tool was originally a ".txt", not the ".xls" that's in it now) that was created.  In that block of code, I created the file, wrote the initial line of data into the file, and then passed the refnum of that file on to a shift register.  That shift register was passed on to other cases in my stacked sequence in order to write more data to that same file in other cases.  Wiring that refnum right into the Write File tools that were elsewhere in my code worked perfectly fine for me when the file was a text file.

So I already have several instances throughout my code of wiring that refnum into a Write File tool.  Can you give me a brief explanation of why it works fine for a text file, but not for an Excel file?

[Joe_H]

OK now I see your confusion.

It does not matter what type of extension you put on a file it is still a file and the refnum that it creates is still a file refnum so even if you put a .xls on the file extension does not mean that it is an excel file it just means that you will be able to trick excel into opening this without windows giving you a prompt. ie open with. when you go to open your file outside of labview. You have to , must use an active x automation refnum to use the properties and functions of excel itself.

What is your main reason for wanting to use excel functions inside labview. you can write things to a tab delimited text file that excel or any word processing program can open and if you give it a .xls extension it will be opened by excel without a prompt.

[spaceman_spif]

My program runs a test unit through several days of testing.  The data file keeps track of the test run: each successful iteration, any stoppage due to an operator, faults that occured, etc.  So each data file is unique depending on how long the unit ran and what happened during that run.  I wrote the data file only to be a record of each test run.

Some units do not complete the test due to power outages, computer gliches, operator error, faults, etc.  But my bosses would like to be able to look into the data files and see how far the unit ran before it stopped, and start from where it left off - rather than restart the whole test from scratch.

In order to do that, I need to recover two certain values from the data file.  With a text file, I would have to do quite a bit of coding to look for and extract a certain string and get those values, because as I said each text file is unique.  I figured if I wrote my data to a spreadsheet, I could code it to always write those two values to particular cells.  And if a restart was necessary, it would be easier to write code that would know to open up the spreadsheet, go right to those two cells, extract the values, and use those values to know where to start the test sequence.

I know I could write the program to just let someone manually enter in those two values, but we would much rather prefer that function be automated.  We want to limit operator involvement.

By golly, I hope all that made sense.

[spaceman_spif]

Maybe this will help...I attached a sample of one of my text files.  My program cycles a large HVAC unit through several switch settings.  In this example, you can see where it records the test data, and I tripped some faults and interruptions to see how that info was written to the text file.  So you can see how interruptions can make each file be unique.

The HVAC runs until a certain amount of compressor starts and a certain amount of run time in high heat has been achieved without any faults.  My program does not record each successfull compressor start or update total high heat time into the text file, since I really did not need that info.

But now, to restart a unit it will need to know how many compressor starts and high heat time it accumulated.  I figured I could set up a spreadsheet to look pretty much identical to my current text file, but record the comp. starts and high heat times into a certain cell.  That would make recovering that info much easier than using string searches to look through a text file.

[spaceman_spif]

Bump.

Still working on this code.  Curious about any more suggestions.

[minnellac]

If you still need some help with this, let me know. I may be able to provide some assistance. You probably realize the differences between writing to a file using the file I/O vi's and creating an excel file by now, but if not here's a quick explanation. LabVIEW's File I/O vi's actually write/read data from disk. The "...Spreadsheet" File.vi's are a little confusing because they actually just mean delimited text file (that spreadsheet applications can import). If you use the Report Generation Toolkit vi's, or manually generate vi's that read and write to Excel files, you are actually just controlling MS Excel from LabVIEW. LabVIEW doesn't actually do the writing or reading from disk, instead, it tells Excel what to do with ActiveX and Excel does the work and sends the results back. This explains why the automation refnums and file refnums are incompatible. The other problem with it is that your application will break if you install it on a machine that doesn't have Excel installed, and there can be potential problems if you upgrade Excel.

An alternative for your application is to use the Config File.vi's or even just write the binary data to disk directly from LabVIEW. If you don't need to open the "log" file outside of LabVIEW, you can use the binary solution. The Config File.vi's let you assign keys and sections to the data you write to disk. Recovering a specific element from the file is only as hard as requesting a specific section and key. The files generated this way are pretty readable too, if you need to open them with a text editor like notepad.

Hopefully this helps a little.
Chris

[Rikki]

Hi Jhoskins

I am facing a problem of writing a data in a excel sheet generated by LabVIEW. If i create a excel file manually it works very fine. But if the file is created by Open\Create function. I am not able to save the data in the file.

Please help

Thanks in advance

Rikki

[Joe_H]

Could you post a simple version of your code so that i can see what you are doing because if you are trying to use the open/create to get a reference to the file to inorder to use the active X functions for excel then I do not believe that this will work.

For more information and some sample VI's and tool kits, you can go to the excel board

[Rikki]

Thanks for the reply.

Yes i am giving the path of the new file to be created to the Create/Open New File function. See the picture attached. It works fine before where i am closing the workbook. I used your excel tool kit before. From that also i was not able to create a excel file and save repeatedly. It was telling error like "Read Only file". Though i gave correct values and also checked the file attributes. And another message was there it will write like "Tab limited Text" so I want to continue or not.

Thanks.

Rikki