How can I append data to an Excel spreadsheet using ActiveX control?

[trosier]

Hi all - I have a VI that I found on the forum that is giving me 95% of what I need. I have made some modifications to it to fit my program a little more but is generally doing the same thing. It is opening up Excel via ActiveX sever and saving data that I have acquired. The part I am struggling with is if this file already exists I want to append it to that file. So using the VI I have attached I have an array of data. I start inserting the data in column A row 1. Then that is saved. Then I want to open that same file up and append more data starting at column C row 1. I want to then save the file again w/o an Excel prompt as far as saving with the same filename. I am a novice when it comes to the ActiveX controls so I am now to the point of asking for help. I have been all over the forums and cannot find an example that helps me. Please ignore any sloppiness with front panel or block diagrams as that is a cleanup stage for me after I get a VI operating correctly. I have not reached that point with this one as of yet. Thanks in advance for any help with this one.

Troy

[smercurio_fc]

Context...need context. Is this VI called in a loop? If so, are you incerementing the column? Or, does this VI need to determine the last column? For the saving part to save the file again use the Save method instead of Save As.

[trosier]

This VI is called in a loop. If it was not clear I stated how it is used in the beginning of my post although I did not state that it was used in a loop. However as I said I will save the data first starting at Column A row 1. I will save the next bit of data starting at column C row 1. The rows are what gets incremented after the starting row and column are input.

[Sarah_Y]

If you got to the MSDN library site (http://msdn.microsoft.com/en-us/library/ms123401.aspx) you should be able to find a list of the activeX commands that work for excel.  It's possible that this might not be an option that's availible.  If you can't find a property that does that, another option would be to save it as a temp file, and then delete the original file, and then rename the temp file that you just created.  

[trosier]

Sarah - Thanks for the reply. I am pretty new to ActiveX and I am finding that this is less than intuitive. Anyway where would you actually find out from the link you gave me the commands, syntax, whatever to use with ActiveX and Excel? I searched all over and I cannot find out where to look. That help alone would be appreciated. I do want to say that from looking at examples and digging around on the forums I did find out how to rename my worksheets, which is the latest problem I was having. The last part I am after is to move that sheet to the end of the worksheets. I am fumbling around with that one now. I have attached what I have already to show how I am renaming the worksheets. Now I just have to move it to the end. Any help would be appreciated.

Thanks,

Troy

[smercurio_fc]

In your previous message you were talking about having a loop where you were appending data to columns. Since you have a loop you can just use the loop index to specify the column where you want to write the data. Are you having a specific issue with this?

As for examples, have you looked in the Excel thread? (Note: Do not post questions in that thread. It is a repository of links only.)

[trosier]

That part of the problem is now fixed. I am at the last part which is after I have the data populated I am trying to copy that worksheet, rename it, and then move it to the end of the sheets at the bottom of the workbook. I have all of that working except for moving the renamed sheet to the end. I found that Excel forum a few days ago when having another problem. I will check there for this one.

Thanks,

Troy

[Sarah_Y]

Hey Troy,

I would look for some sort of move method.  You might want to try looking for something in other coding communities if you can't find one that's LabVIEW specfic.  Since it's all ActiveX controlled, if they do it there you can do it with LabVIEW too.  I agree, the MSDN site is hard to navigate, but since ActiveX is a Mircrosoft product, they are the ones who maintain that documentation and that's the official place to find it.

[trosier]

Sarah - Between talking with NI and digging around on forums I took all the bits and pieces that I came across and made a VI that does the following:

-->Open up Excel

-->Copy the sheet specified by Master Sheet Name

-->Rename the copied sheet according to New Sheet Name

-->Move that newly named sheet to the end (right most position) of all the sheets.

I posted this VI on the Excel Forum for others to look at and use since this was a tough one to get together. It is located here:

http://forums.ni.com/t5/BreakPoint/Excel-Board/m-p/1506250/message-uid/1506250/highlight/true#U15062...

Troy

[Sarah_Y]

Thanks for posting the code!  This is a great resource.  As you know, ActiveX examples are hard to come by 🙂