topic How to Change the Text Color in a Footnote, Using ODS Excel Options (Print_Footer) in ODS and Base Reporting

How to Change the Text Color in a Footnote, Using ODS Excel Options (Print_Footer)

George12 — Thu, 12 Aug 2021 16:14:07 GMT

Hi all,

I've spent a while working on the following code:

ODS EXCEL FILE = "/filepath/filename.xlsx";
ODS EXCEL OPTIONS
	(PRINT_FOOTER = "&amp;LForename Surname&#10;Position&amp;C&amp;BDocument Name&#10;Document Subname&amp;RTime&#10;Date");

The code works fine, and it creates this footer at the bottom of my Excel document:

This is exactly what I want, EXCEPT that I want the text for the middle footnotes (i.e. Document Name and Document Subname) to be in red, not black. How do I change this?

I've tried searching for a solution on Google. The only potential option which I've found is to insert &Color=RED, right before 'Document Name'. However, when I try it, it doesn't work. The &C is read as a separate command to centrally justify that footnote, and I'm left with a footnote that looks like: OLOR=REDDocument Name.

Any help would be greatly appreciated. 🙂

Re: How to Change the Text Color in a Footnote, Using ODS Excel Options (Print_Footer)

jimbarbour — Fri, 13 Aug 2021 23:26:30 GMT

Hi, @George12,

May I ask which SAS product you are using? I am using SAS 9.4 M6.

In SAS, in just about every context I can think of, &C would invoke the macro language processor. What if you were to try just "color" without the preceding ampersand?

This code:

ODS	Excel	CLOSE;

OPTION	Device=ACTXIMG;
TITLE	"Test Report";
FOOTNOTE	color=red "Red " color=green " Green " color=blue "Blue";
ODS	Excel
	OPTIONS(
		Frozen_Headers="1"
		Frozen_RowHeaders="1"
		Sheet_Name="Test"
		Embedded_Titles="YES"
		Embedded_Footnotes="YES"
		Embed_Footnotes_Once="YES"
		Gridlines="YES"
		);

PROC	PRINT	DATA=SASHELP.Cars (OBS=15);
RUN;

ODS	Excel CLOSE;

The color in the footnotes appears to match what I coded; see below. Note that I just used a regular footnote rather than PRINT_FOOTER.

Jim

Re: How to Change the Text Color in a Footnote, Using ODS Excel Options (Print_Footer)

George12 — Mon, 16 Aug 2021 14:52:10 GMT

Hi @jimbarbour,

Thank you for your response. I am using SAS 8.3.

I do really appreciate your taking the time to respond, but I'm afraid that it doesn't help to solve my specific problem.

I tried using your code. Changing the text colour only seems to work if the footer is embedded, and I don't want an embedded footer. I also can't see how to use that code format to create two lines for seperate left, central, and right footnotes.

The &color is a VBA code, taken from here: https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb225426(v=office.12). Note that the other codes here (e.g. &L, &C, &R, &B, etc.) seem to work fine, but not &color.

I tried adding color=red - without the ampersand, as you suggested - into my existing code, but that's just read as text (i.e. so the footnote results in color=redDocument Name).

Re: How to Change the Text Color in a Footnote, Using ODS Excel Options (Print_Footer)

jimbarbour — Mon, 16 Aug 2021 15:56:33 GMT

@George12,

Well, shoot, but I sort of thought "that can't be the solution; it's too easy."

@George12 wrote:
The &color is a VBA code, taken from here: https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb225426(v=office.12). Note that the other codes here (e.g. &L, &C, &R, &B, etc.) seem to work fine, but not &color.

Well, then it seems that it's a Microsoft VBA issue, yes? I wonder if there are any VBA forums where someone may have encountered this.

It is as though the VBA interpreter isn't able to properly distinguish between &c and &color. It looks like VBA gets to "&c", interprets it as a command to center, and doesn't bother looking at the "olor". If a look at VBA forums doesn't unearth a resolution, I wonder if there's a way to instruct VBA to handle "&color" as a whole rather than as "&c" and plain text "olor". In SAS, I might do something like %NRSTR(&color) or %BQUOTE(&color). I wonder if there's a way to do that in VBA.

Jim

Re: How to Change the Text Color in a Footnote, Using ODS Excel Options (Print_Footer)

George12 — Mon, 16 Aug 2021 16:59:46 GMT

Hi again @jimbarbour,

I've worked it out! Thanks for pointing me in the right direction. 😀

Following your suggestion, I searched on Google for specifically VBA &color solutions. Apparently, you need to use &K (who knew?) and then the hexidecimal color value.

So, the final code looks this:

ODS EXCEL FILE = "/filepath/filename.xlsx";
ODS EXCEL OPTIONS
	(PRINT_FOOTER = "&amp;LForename Surname&#10;Position&amp;C&amp;B&amp;KFF0000Document Name&#10;Document Subname&amp;RTime&#10;Date");

Works perfectly now. 😊

Re: How to Change the Text Color in a Footnote, Using ODS Excel Options (Print_Footer)

jimbarbour — Mon, 16 Aug 2021 18:21:10 GMT

Excellent! Sometimes all we need is a different perspective or approach to shake loose the solution.

Jim

Re: How to Change the Text Color in a Footnote, Using ODS Excel Options (Print_Footer)

Sofya982 — Sun, 13 Nov 2022 10:15:04 GMT

Click the "Home" tab. Click the small "Down Arrow" in the bottom right corner of the Font section of the ribbon. Click on the "Superscript" box to insert a check mark, then click “OK.” The footnote takes on the traditional, small and slightly higher appearance compared to normal text. This is how you can add footnote in excel in seconds.