How can I delete cells in a manner similar to inserting them with the "Excel Insert Cells.vi" included in the Excel specific report generation toolkit?

[BrandynMH]

I'm running into an issue I don't see solved anywhere on here or google. I have found some solutions, but none that don't require me to entirely redo most of my program.

The problem: I want to be able to delete a column from an existing open excel spreadsheet similar to the way I can add them in the "Excel Insert Cells.vi" included in the Excel specific report generation toolkit. 

I'm honestly astounded the reverse doesn't exist since adding columns is so easy. My program collects data from a series of files and inserts that data into an Excel template. The idea is that every user with my program will be able to create an excel template with the necessary calculations, and use this program to display them after compiling hundreds of .csv files of our production data. 

As it stands the program opens the template, inserts the relevant data from the .csv files and leaves it open for the user to save the result wherever they please. The last step is to delete useless data from certain columns based on their data filtering selections. I am dealing with the data in the report data type, so I can't use normal delete array element options. This data must be in the program until the final step before allowing the user to save. Attached is the bit of code I'm having trouble with. I need to add a way to delete a column of data, and if someone can help me with that, I can figure out the rest. 

All answers appreciated!

[Bob_Schor]

As you have already discovered, NI has provided a way to generate Reports using Excel.  In the process, they have also given us a lot more, such as ways to selectively edit an Excel Workbook and other Fun Things to Do.

While they provided the "Insert" mechanism, they didn't add the inverse, the "Delete" (reasoning, I suppose, that you don't "delete" data from a Report).  You could, of course, simply read the entire Report in as data, then rewrite the report leaving out the stuff you don't want (what a pain!).

Or you could learn some low-level Excel functions and write your own Excel Delete Cells.  Here's how:

• Drop an Excel Insert Cells VI on an empty Block Diagram.
• Open it as though it was an ordinary VI (which it is, sort of).  Now you can see (some of) the code that NI uses to generate ActiveX calls to Excel functions that handle the business of Insert Cells.
• Using this as a "template" for Delete Cells, figure out the ActiveX calls you'd need to make in order to Delete Cells.
• When you think you've got it, try it out.  Be sure to design a Test Suite that tries all the cases, including deleting 0 cells, or 0 rows, or 0 columns.  What happens if you specify -1 cell?
• If you produce something that works, try to make it look as "snappy" as the functions from NI, then come back and share it with the LabVIEW Community by posting it here.

Bob Schor

[BrandynMH]

Thanks for the reply Bob! Actually, my first thought was to do as you said and simply reverse engineer the insert cells vi. Unfortunately, the vi takes the report data type and runs it through a "to more specific class" function. This function requires me to pass the report class reference on the "target class" input then unbundle the more specific class. I spent 30 minutes trying to figure out how to get that into my code but couldn't. Since this is an NI generated vi it seems to be protected and I can't simply copy paste the class reference. If you could tell me how to get it (image attached) I can do everything else needed and upload the code to the community when I'm done! 

[PsyenceFact · Accepted solution]

A very useful VI tucked away on the Report Generation - Excel Specific - Excel Advanced palette is Excel Get ActiveX References.vi.  This accepts a report input and gives you the ActiveX references to the Application, Workbook, Worksheet, Sheets collection and Shapes collection.

From a Worksheet reference, you can define a Range and then apply verious methods to that Range, including Clear.

I find the Microsoft VBA documentation very useful when trying to figure out which ActiveX methods or properties to use, and what inputs and outputs are required.  https://docs.microsoft.com/en-us/office/vba/api/excel.range.clear

ETA: https://docs.microsoft.com/en-us/office/vba/api/excel.range.delete

The Delete method accepts input Shift which should be set to -4159 (xlShiftToLeft) or -4162 (xlShiftUp).

PsyenceFact

[BrandynMH]

Thanks! I haven't played with this much yet but this is exactly what I need. 

[PsyenceFact]

A handy hint if you're going to look further at using ActiveX methods in Excel: if you want to write data to a specific cell you can define the Range as before then set the Range.Value2 property.  This will accept a 2D array of variants so you can use a single VI to write different number representations (double, I32, etc.).  If you write strings using the same VI, I recommend setting the Range format so they are displayed literally.

If your data is a single value or a 1D array, convert to a variant then use Build Array to create a 2D array.