Word report, comparing dates and shading

[agyna]

Hi Guys,

I need some help. I have I have to compare some dates in a table, if I find a date that is older than today date,I have to shade or color  the background of that cell in green or red and put the table in a word report.

Attached is what I am trying. For now I am just dealing in coloring just for January. If it works, I will apply it to other months.

Need some help please.

[James.M]

I wasn't able to get the "Jan" case or "January" cases to activate the way your code is now, so I added a Trim Whitespace node before the case structure and it worked.

• When the "Jan" case runs, it creates a Word document consisting of the input table and colors the whole table green. If you want to only do the specific cell, then you'll have to figure out the Range calues for that cell... which is going to be tough because this is in Word not Excel.
• Your case structure is going to cause you to have a whole bunch of redundant, duplicated code. Get rid of the string-based case structure and figure out a different way to handle the month. You can sue a Format From String node to do this better. Check out the format codes here.
• Your report generation should be separated in to different steps. At the beginning of your program, create the document. In the middle, edit the cells and backgrounds and all that. At the end, clode the report. You have all three of these steps within the case structure. Instead, use a shift register to keep the report data in memory as you loop through the data.

[agyna]

Yes my real probleme is how to color only the cell that is needed. I put everything to gether for you guys to have all in one. But really coloring the cell that where I am getting stacked.

[James.M]

Is there a reason you aren't doing this in excel? That's the better/easier option here when you dive in to cell manipulation.

[ben64]

A few more comments.

In your months array you are using abbreviations like "Jan.", you should be aware that in regular expressions the dot is a special character that matches any character (probably not what you want). Even it it matches "Jan." you don't have a "Jan." case in your case structure. The simplest fix is to remove the dot in your constant month array (note that in this case the Jan regex will return a match for the January string).If you specifically want to match "Jan." use Jan\. i your month array, the backslash is an escape character (\. will match the dot character). I don't know where your output array is coming from but you will have to find a way to insure the calibration dates are entered in the expected format.

Ben64

[James.M]

So definitely pay attention to my comments above and ben's comment about your string formatting. If you ignore these suggestions and only fix the one thing you're asking about, you will be back here with more questions or spend a whole lot of extra time debugging your code.

As for the specific cell background color, you can access the Word.Tables object instead of the Document object you were using. From this, you can go to a specific cell and set the color. Like so:

I added this to your code and it worked. The numeric input coming from the left is wired to the For loop index, so that is goes to the correct row for whatever cell you are parsing now. A key thing to notice here is that the report indices are base 1 instead of base 0.

[ben64 · Accepted solution]

---

@James.M wrote:

So definitely pay attention to my comments above and ben's comment about your string formatting. If you ignore these suggestions and only fix the one thing you're asking about, you will be back here with more questions or spend a whole lot of extra time debugging your code.

As for the specific cell background color, you can access the Word.Tables object instead of the Document object you were using. From this, you can go to a specific cell and set the color. Like so:

I added this to your code and it worked. The numeric input coming from the left is wired to the For loop index, so that is goes to the correct row for whatever cell you are parsing now. A key thing to notice here is that the report indices are base 1 instead of base 0.

---

Even simpler than that, use Word Table Borders and Shadind.vi from the Word Tables palette!

Ben64

[James.M]

---

@ben64 wrote:

Even simpler than that, use Word Table Borders and Shadind.vi from the Word Tables palette!

---

Well I'll be, the whole reason one pays for the report generation toolkit.

[agyna]

Thanks, this helps.