write to excel

[at2bob]

i created a VI that would open excel, write to a sheet then save as original_timestamp (this way i had a clean template each time), the problem i ran into was i opened the original each time so everytime i wrote to new sheet only the most recent sheet had values while the others were empty.  So i split the VI into 3 parts - OpenExcel, WriteToExcel, and CloseExcel.  Now all of my sheet data gets written to the first sheet.  Can someone look at my VIs and let me know if i have a reference problem.  I call these from TestStand and safe the references to variables which i then pass on to the other VIs.

[RavensFan]

You are getting a reference to the active worksheet.  I don't see anywhere that you are opening up a reference to a different worksheet.  So whatever sheet was active the last time the file was saved is the one getting the new data.  And will remain the active sheet when you save and close it.

[at2bob]

Would that go into the OpenExcel VI or the WriteData VI?  I thought that since i was passing in the Sheet and setting it active that would clear up the referencing.

[FoxSquirrel]

You would want to select the correct worksheet in the WriteData VI I believe for your application.  However, I don't see at all in your code where you set it to active.  I see you use the sheet name to get an item, but you do nothing else with it.

[at2bob]

i've looked it over and don't see where or how to set the active sheet other than the way it is now.  Is there another method i need to use?

[RavensFan · Accepted solution]

Except you are not setting the active sheet now.

You do have a invoke node that takes a sheet name and gives you a reference to a worksheet.  Except you never do anything with that reference coming from the "Item" terminal.  You proceed to use the ActiveSheet property and use that sheet.

Try eliminating ActiveSheet, and use the output of the Sheets invoke node.

If you want to make this sheet the active one (you don't need to because you can work on inactive sheets, but activating it will bring it to the front and save that state when you save the file), you can use the Invoke Node called Activate that is part of the Worksheet object.