DDE Footnotes in Excel

[bspangler]

Hi,

I am outputting footnotes via DDE to Excel and I want to switch the order of the "Copyright 2014 LLC. All rights reserved." with the "Confidential && Proprietary" line,

but everytime I do, I get errors regarding too many parentheses and/or quotation marks.  This is a macro to print a title in MS Excel and also to print footers left alligned stacked on top of each other.  Sometimes I will have 3 lines, and sometimes I will have four lines in the footer.  Below is an example of what I am trying to achieve.  This code was handed down to me, so any input is great.

Any ideas? Thanks!

What I want:

Source: Membership File

Calculated as of: December 1, 2013

Confidential && Proprietary

Copyright 2014 LLC. All rights reserved.

What I am currently getting:

Source: Membership File

Calculated as of: December 1, 2013

Copyright 2014 LLC. All rights reserved.

Confidential && Proprietary

%macro footer(sheet,left1,left2);

      filename DDEcmds dde 'excel|system';

     data _null_;

          file DDECmds;

                      put%unquote(%bquote('[workbook.activate("&sheet")]'));

          put %unquote(%str(%'[page.setup(%nrbquote("&C&B&16&""Times New Roman""&title.%'%'0d%'x%'&12&A"))]%'));

     %if &left2 ne %then %do;

put %unquote(%str(%'[page.setup(,%nrbquote("&10&L&""TimesNew Roman""&left1.%'%'0d%'x %'&left2.%'

%'0d%'x %'%str(Copyright 2014 LLC. All rights reserved.)%'

%'0d%'x %'&10&L&""Times New Roman""Confidential %' %'26%'x %'26%'x %' Proprietary"))]%'));

                %end;

           %else

                %do;

put %unquote(%str(%'[page.setup(,%nrbquote("&10&L&""Times
New Roman""&left1.%'%'0d%'x %'%str(Copyright 2014 LLC. All rights reserved.)%'%'0d%'x
%'&10&R&""Times New
Roman""&P+&page.%'%'0d%'x %'&10&L&""Times
New Roman""Confidential %' %'26%'x %'26%'x %' Proprietary"))]%'));

                %end;

     run;

%mend footer;

%let page=0;

%let title=Membership Profile;

%let agedate=%str(December 1, 2013);

options mprint;

%footer(Marital Status,Source:  Membership File);

%footer(Reported Age,Source:  Membership File,%str(Calculated as of: &agedate));

[Peter_C]

trying to make your code work, I focus on this page.setup line.

put %unquote(%str(%'[page.setup(%nrbquote("&C&B&16&""Times New Roman""&title.%'%'0d%'x%'&12&A"))]%'));

to test it on its own, I have adapted it slightly and can persuade it to generate the following PUT statement. Is it correct?

put '[page.setup("&C&B&16&""Times New Roman""&title.''0d'x% '&12&A")]';

At that stage, did you want &C&B to resolve macro variables or appear as text passed into excel?

Was some &title to be resolved or passed to excel as that text?

For my testing, I used the code :

%put %quote( put %unquote(%str(%'[page.setup(%nrbquote("%nrstr(&C&B&16&)""Times New Roman""%nrstr(&title.)%'%'0d%'x% '%nrstr(&12&A)"))]%')); );

Around "syntax being tested", this provides the wrapper %put %quote( syntax being tested ; ) ;

To avoid attempting to resolve the macro variables I referred above, I wrapped those references in %nrstr().

[bspangler]

Hi, thank you so much for your response!  I want the &C&B to make the title centered and bold, and I want &title to resolve to "Membership Profile."  So in the end, I will end up with an Excel workbook page with a header that says "Membership Profile" at the top center of the page in bold.  Also, I tried using the code you wrote above and although I didn't get any errors, it did not add the title to the Excel workbook page.

[Peter_C]

Thank you for this small part of the full explanation needed.

If you indicate what you wanted to generate from each piece if the nacro being executed, it would make it possible for us to help.

[bspangler]

Hi, the title resolves fine via the %let statement, and the &C&B resolve via the page.setup for DDE.  It is really the footnotes that I am having a problem with.  They all output to Excel, but I just want to switch the order of the Confidential and Proprietary line with the Copyright line.  I believe I defined all the macros in the original post, let me know if I can do anything else to help explain.  Thanks!

%if &left2 ne %then %do;

            put %unquote(%str(%'[page.setup(,%nrbquote("&10&L&""TimesNew            

               Roman""&left1.%'%'0d%'x %'&left2.%'

                                                        %'0d%'x %'%str(Copyright 2014 LLC. All rights reserved.)%'

               %'0d%'x %'&10&L&""Times New Roman""Confidential %' %'26%'x                            %'26%'x%'Proprietary"))]%'));

                %end;

[bspangler]

Hi!

Good news, I was able to get the footnote lines switched, so now all I need to do is get all the embedded footnote lines to be left alligned and in Times New Roman font.  When there are only 3 lines in the embedded footnote, the 'Copyright' line is not left alligned and is not in Times New Roman, and when there are 4 lines in the embedded footnote, the 'Copyright' line is left alligned, but not in Times New Roman, and the 'Confidential and Proprietary Line' is not left alligned.  Any help is appreciated, thanks!

%macro footer(sheet,left1,left2);

filename DDEcmds dde 'excel|system';

data _null_;

file DDECmds;

      put %unquote(%bquote('[workbook.activate("&sheet")]'));

      put %unquote(%str(%'[page.setup(%nrbquote("&C&B&16&""Times New Roman""&title.%'%'0d%'x %'&12&A"))]%'));

      %if &left2 ne %then %do;                                                                                             put %unquote(%str(%'[page.setup(,%nrbquote("&10&L&""Times New           Roman""&left1.%'%'0d%'x%'&left2.%'%'0d%'x%'Confidential and Proprietary%'%'0d%'x %'&10&R&""Times
          New Roman""&P+&page.%'%'0d%'x %'&10&L& %str(Copyright 2014 LLC. All rights reserved.)"))]%'));%end;

   %else %do;                                                                                                            put %unquote(%str(%'[page.setup(,%nrbquote("&10&L&""Times New Roman""&left1.%'%'0d%'x%'Confidential and      Proprietary%'%'0d%'x%'&10&R&""Times New Roman""&P+&page.%'%'0d%'x%'&10&L& %str(Copyright 2014 LLC. All      rights reserved.)"))]%'));%end; run;

%mend footer;

%let page=0;

%let title=Membership Profile;

%let agedate=%str(December 1, 2013);

options mprint;

%footer(Marital Status,Source:  Membership File);

%footer(Reported Age,Source:  Membership File,%str(Calculated as of:  &agedate));

[bspangler]

Thank you for your help - I was able to work it all out!

%if &left2 ne %then %do;

     put %unquote(%str(%'[page.setup(,%nrbquote("&10&L&""Times New Roman""&left1.%'

     %'0d%'x %'&left2.%'

     %'0d%'x
     %'Confidential and Proprietary%'

     %'0d%'x %'&10&R&""Times New Roman""&P+&page.%'

     %'0d%'x %'

     &10&L&""Times New Roman""%str(Copyright 2014 LLC. All rights reserved.)"))]%'));

%end;

%else %do;

     put %unquote(%str(%'[page.setup(,%nrbquote("&10&L&""Times New Roman""&left1.%'

     %'0d%'x
     %'Confidential and Proprietary%'

     %'0d%'x
     %'&10&R&""Times New Roman""&P+&page.%'

     %'0d%'x
     %'&10&L&""Times New Roman""%str(Copyright 2014 LLC. All rights reserved.)"))]%'));

%end;