Excel Easy Table format

[jyang72211]

I used exel easy table to modify a value in a spreadsheet.  When I set auto format to none, I get a border around the cell containing the value.  I tried a different format.  I tried xlRangeAutoFormatPTNone, and that really messed me up.  How do that get rid of that format through code?  See attached.

[RavensFan]

Your code seemed to work for me.  When I ran it, the box that was around cell B4 disappeared.

[jyang72211]

Yes, xlRange AutoFormatPTNone got ride of the borders aronund the value, but it introduced  a border on the top and bottom rows.  I actually have two questions.

1. How do I change a value without adding any border or new format into the worksheet?

2. If some bordering and formating are already done, how can I erase it? 

[ben64]

The Excel Easy Table.vi uses the Append Table to Report.vi. The problem is that the Append Table to Report.vi has a Show grid line input which is not connected so it uses the default TRUE value. Unfortunately there doesn't seem to be an easy fix. It would be nice the Excel Easy Table.vi has a show grid input on the connector pane but there is no more available input on it. You could try to hide the grid afterward.

Ben64

[jyang72211 · Accepted solution]

Actually, I just found that there is a set color and border vi in the toolkit.  I will just use that.  Thanks!

[SteveAtBoeing]

I had the same border problem.  I just wanted to blop an array of data into an Excel table without changing anything, and "Excel Easy Table.vi" thought it would be best to resize my columns and put borders around the cells where it put the data.  This would not have been my go-to coding position if I were writing a vi called, "Excel Easy Table."  "Hmmm, I know, everybody would want me to change their column widths and add borders!  Yeah!"  No.

Ok, now I've vented a bit.  So I read this thread, and am now trying to use "Excel Set Cell Color and Border.vi" to get rid of the unwanted borders.  I wired the following into both the "inside border" and "outside border" terminals:

 

 Ah, yes, I thought.  "False" up there means "hide the border," according to NI's documentation (hmmmm) of the function.  "Hide"?  Well, whatever.  I want "No" border, but whatever.  So, upon running it, the function instead appears to have removed the GRIDLINES from the spreadsheet where it operates!  Wow!  I didn't even know it was possible to remove gridlines from only part of a spreadsheet!  Good grief!  I just want what Excel calls "no border"!  How do I do that?

Thanks,

Steve Brady

[SteveAtBoeing]

Aha!  I found the problem.  Indeed, I DO want "False" for borders.  I need to explicitly set BACKGROUND to "Transparent".  Why doesn't it default to "Transparent"?  That appears to be the Excel default...

Steve Brady

[AEI_JR]

This has been some help to me, as I have always struggled with how Easy Table forces a format no matter what settings I give it.  However, I need to be able to add data to a running table without overwriting previous formatting, and it just appears to be impossible!  Why, why, why can I not simply insert a value or series of values (array) into an existing Excel sheet without LabVIEW messing with the column widths and cell formats?

[JonP]

Ben64 provides the answer: use "Append Table to Report.vi" instead of "Excel Easy Table.vi" and set the "show gridlines (YES)" input False. The only thing that Append Table to Report is missing is the font setting but "Set Report Font.vi" does that for the whole report at once, which is what I want and is more efficient.