Append Data to excel worksheet

[hiNI]

Hello,

I would like to append data in Excel to an already existing worksheet if it exist.  I would like to add this new block of data 1 column to the right of the last column of data, i.e., check for the last column in the existing worksheet for data, skip a column, then add the new data.  I would like to add to the already existing worksheet after each run if the excel sheet exist.

I have attached what I have done far.  I am unsure of how to check for the last column with valid data then to append the new block of data.

Thanks for your help.

[Bob_Schor]

Hi, NI.

     I have a few comments on your Append Columns VI.

• I like the State Machine.
• Regarding the "Get Worksheet Names" State:
  • I'm puzzled by the "Bob Schor Template" code, where you create a list of all of the WorkSheet names.  I don't know this "Bob Schor" guy, but I hope he doesn't advocate "Loop until an Error Occurs" style of LabVIEW code.
  • I recommend you use a For Loop, driven by the "Worksheets Count" output from Excel Workbook Properties VI.
  • Read the Help for Excel Workbook Properties.  Any of the Properties sound useful to you inside the For Loop?
  • Did you notice that your code delivers the names of 7 Worksheets, while the example VI you attached (thank you very much for doing that, by the way) contains 8 Worksheets?  You missed Sheet7 ...  I'll bet this Bob Schor guy wouldn't have missed it ...
• In "Check if Worksheet Exist", you don't need any Loops.  If you compare the Array of Worksheet Names with a single value on the "worksheet name" control, you'll get an Array of Booleans, "F F F F T F F F" (if you count the Worksheet that you missed).  You can put this Boolean Array through the "Existential Quantifier" (also known as "Or Array Elements") and it will be "True" if your chosen worksheet name was found.  
• Now you know if the Worksheet has been found, and have a Boolean Array that has "True" in the position where the Worksheet exists (assuming the previous Existential test came back True).  So how do you make that Worksheet the current Worksheet?  If you can figure out which bit is True, you can use that to again Excel Get Worksheet and voilà, you are ready to go.
• So now you have your Worksheet, and want to add new data.  There's an Excel Get Last Row function, but that doesn't directly help you -- you want "Excel Get Last Column", which doesn't exist.  This Bob Schor person proposed (on NI's Idea Exchange) that NI add a "Get Last Column" in 2017, saying "I could easily write such a function myself, but so could NI, and if NI did it, everyone who uses the Report Generation Toolkit would have access to such functionality."  It has received 8 Kudos since being proposed, not a wild endorsement by the Community!  You might have to make up your own function!
• Assuming you know the Row and Column you want to add data, you can use Excel Easy Table to add the data whereever you want.  Note that this function provides a Start index (using LabVIEW Notation, where (0, 0) means Excel Cell A1 -- there are two functions on the Excel Advanced sub-Palette that lets you convert from Excel to Array Index notation.
• I'm confused by the Insert Data state.  This looks like you are re-creating the Worksheet, but maybe this is to be used for the first time entry of data?  Your Header is strange -- look at it in the Excel Worksheet and decide if this is what you want it to be.  Looks "wrong" to me (but what do I know?).
• Don't use the top-level "Append" functions -- use the lower level Excel Easy Whatever functions.  If you used it to write the Header, you would be able to deduce the next blank Row and the next blank Column, and could use these to decide where to put additional data.

Whew.  That's enough work for me today.  Hope this is helpful.

John Smith