How do you open an excel workbook-sheet 2 ,read the number of rows,append 1 row,and then close the workbook?

[Iglu@1coolplace.com]

I have found some of the sub vi's to read the # of rows in an existing workbook sheet 2, but I can't figure out how to append to the same workbook sheet 2.

[Ben]

The top Enthusiast in this type of question (i.e. ActiveX control of MS products) is Micheal Monroe. You can find a list of his answers here;

http://exchange.ni.com/servlet/ProcessRequest?RHIVEID=101&RPAGEID=261&HRedirected=True&HUserId=101_2952&RFORMNUMBER=6

I beleive he has had something to say about this question recently. Please take a look at his answers. If one of his answers solves your challenge, give him stars!

Trying to help (without learning more about MS than I have to :>)),

Ben

[Liz F]

Hello Michael,

Thank you for contacting National Instruments. This is an interesting application! I expanded on some existing example programs available on our website, and wrote a short program that does what you are asking. It opens the spreadsheet at the path you specify, and appends the data in the array to the end of the sheet you specify.

This program is not robust by any means, and can only currently handle updating 4 or fewer values into the new row. You would probably want to modify the "ABCD" subVI to make it more robust. You would probably also want to add code to check that the sheet the user is looking for exists, etc. (The code is also fairly messy, as it is just a rough idea). I am running LabVIEW 7.0 with Windows XP a
nd Excel 2002.

Please let me know if this gets you on the right road towards what you are looking for. I attached the VI I wrote, "LizX.vi", and a subVI used in it, "ABCD.vi", as well as the Excel spreadsheet I used for testing, "LizBook.xls". Good luck, and please let me know how it goes! Have a great day!

Liz Fausak
National Instruments

[Michael_Munroe]

Here is the example program "Write Table to XL" modified for your needs. I inserted the Used Range function to determine the size of the existing data and added that number to the starting row address. This example will open the specified workbook, select the specified sheet by index, append a block of data, then save and close Excel.

Once you have an automation refnum to a particular sheet, all operations based on that refnum will apply to that sheet.

Michael
http://www.abcdefirm.com

[Iglu@1coolplace.com]

Thanks for your help, I ran the vi and it won't append to the existing file, it just keeps overwriting the file. Is there a boolean not wired somewhere that is supposed to tell an invoke node or mode , to append instead of overwrite?

[Iglu@1coolplace.com]

Thank you for your help, I keep on getting error "#2146959355 occured at server execution failed in lizx.vi" when I run this vi, and it won't append to the excel spreadsheet.

[Liz F]

Hello,

I thought you might get this error. It is almost always caused by different versions of Office. You may want to just take a look at the block diagram and see if you can reconstruct it on your own, if you have a version of Office other than Office 2002.

You might make a special effort to make note of where the error is occurring. Unfortunately, because of the differences in the ActiveX calls between Microsoft's different versions of Office, the only method possible to fix it is usually just rebuilding the VI.

Please take a look at this KB for more information.

http://digital.ni.com/public.nsf/websearch/83211E3A088D0C3786256DB700621FE8?OpenDocument

I hope this helps!

--Liz Fausak

[Michael_Munroe]

There no boolean that determines if it appends or not. It works perfectly for me on WinXP, Office XP and LV6.02. If you are using an older version like Office 97, you will need to change the Range->Value2 to Range->Value in the Set Cell Value.vi, otherwise no new data will be appended.

Open up the Excel Get Used Range.vi before running the main vi and check what number is returned for Used Rows. This number needs to be the last row used by the original data. This number is added to the loop index to increment the row address, starting at the last location +1.

If you are getting Used Rows = 1, there is something wrong with how you select the sheet or something odd in how the data is organized. Are you appending into hidden rows?


Michael

[Iglu@1coolplace.com]

Thanks for your help , This worked very well with few changes.

[Iglu@1coolplace.com]

I don't know how to rate the answer,, but I give it 4 stars.