Excel error - ODS tagsets.ExcelXP

Reply
New Contributor
Posts: 3

Excel error - ODS tagsets.ExcelXP

[ Edited ]

Hello SAS community,

 

I have been using the ODS tagset code below for over a year, and it always worked perfectly…

 

…up until we switched from using the local server to the SAS LASR server.

 

Now, the xlsx file is not created (despite the fact that the vbs file is still created as usual). I am using SAS EG 7.1.

 

After switching to the SAS server, I had to modify other codes to correctly reference Excel sheets. For instance, I had to change a line in my proc import code from “dbms=Excel replace;” to “dbms=xlsx replace;” --  So, I tried the same thing with my ODS tagset code. I tried changing “put " Set oXL = CreateObject(""Excel.Application"")";” to “put " Set oXL = CreateObject(""xlsx.Application"")";” but I had the same result: an xml file, a vbs file, and no xlsx file.

 

Has anyone here had that issue, and if so, do you have any suggestions?

 

Thank you in advance.

 

 

Code:

%macro convert_files(default=&path, store=&path/Report_Date.vbs,ext=xml);

 

/*MACRO: CONVERT_FILES */                                                                                                                                                    /* USAGE: %convert_files(arg1=value, arg2=value, ... );   */                                                                                                            

/*DESCRIPTION:       */                                                                                                                                           

/*This macro is used to convert files generated with ODS destinations     */         

/*to native Excel files. For more information see the below link.      */              

/*http://www.pharmasug.org/proceedings/2011/SAS/PharmaSUG-2011-SAS-TT02.pdf?page=12 */  

 

data _null_;

file "&store";

put " Dim oXL ";

put " Dim oFolder";

put " Dim aFile";

put " Dim FSO";

put " Set oXL = CreateObject(""Excel.Application"")";

put " Set FSO = CreateObject(""Scripting.FileSystemObject"")";

put " oXL.DefaultFilePath = ""&default""";

put " oXL.DisplayAlerts = False";

put "  if FSO.FolderExists(oXL.DefaultFilePath) then";

put "   Set oFolder = FSO.GetFolder(oXL.DefaultFilePath)";

put "     For each aFile in oFolder.Files ";

put "       If Right(LCase(aFile.Name), 4) = "".&ext"" Then";

put "        oXL.Workbooks.Open(aFile.Name)";

put "        oXL.Visible = false";

put "        if (oXL.Version) >= 12 Then" ;

put "        oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) - 4) & "".xlsx"",51";

put "    Else";

put "       oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) - 4) & "".xlsx"",-4143";

put "   End If";

put " oXL.ActiveWorkBook.Close SaveChanges = True";

put " End If";

put " Next";

put " Set oFolder = Nothing";

put " end if";

put " FSO.DeleteFile(""&default\*.&ext""), DeleteReadOnly";

put " FSO.DeleteFile(""&default\*.vbs""), DeleteReadOnly";

put " oXL.DisplayAlerts = True";

put " oXL.Quit";

put " Set oXL = Nothing";

call system("&store");

run;

 

%mend;

options noxsync noxwait;

%convert_files();

 

/*default is the location of the xml files, and store is the name and location(same as default) of the new xlsx file*/

Data _NULL_;timesleep=sleep(120,2);run;

 

 

Log:

1                                                          The SAS System                              08:03 Friday, August 12, 2016

 

1          ;*';*";*/;quit;run;

2          OPTIONS PAGENO=MIN;

3          %LET _CLIENTTASKLABEL='Convert&Email';

4          %LET _CLIENTPROJECTPATH='G:\Path\Project.egp';

5          %LET _CLIENTPROJECTNAME='Project.egp';

6          %LET _SASPROGRAMFILE=;

7         

8          ODS _ALL_ CLOSE;

9          OPTIONS DEV=ACTIVEX;

10         GOPTIONS XPIXELS=0 YPIXELS=0;

11         FILENAME EGSR TEMP;

12         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR

13             STYLE=HtmlBlue

14             STYLESHEET=(URL="file:///C:/SASHome94VA/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")

15             NOGTITLE

16             NOGFOOTNOTE

17             GPATH=&sasworklocation

18             ENCODING=UTF8

19             options(rolap="on")

20         ;

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

21        

22         GOPTIONS ACCESSIBLE;

23         %macro convert_files(default=&path,

23       ! store=&path.vbs,ext=xml);

24        

25         %*;

26         %*

27         %*  MACRO: CONVERT_FILES

28         %*

29         %*  USAGE: %convert_files(arg1=value, arg2=value, ... );

30         %*

31         %*  DESCRIPTION:

32         %*    This macro is used to convert files generated with ODS destinations

33         %*    to native Excel files. For more information see the below link.

34         %*    http://www.pharmasug.org/proceedings/2011/SAS/PharmaSUG-2011-SAS-TT02.pdf?page=12

35         %*

36         %*

37         %*

38         %*

39         %**;

40         data _null_;

41         file "&store";

42         put " Dim oXL ";

43         put " Dim oFolder";

44         put " Dim aFile";

45         put " Dim FSO";

46         put " Set oXL = CreateObject(""Excel.Application"")";

47         put " Set FSO = CreateObject(""Scripting.FileSystemObject"")";

48         put " oXL.DefaultFilePath = ""&default""";

49         put " oXL.DisplayAlerts = False";

50         put "  if FSO.FolderExists(oXL.DefaultFilePath) then";

51         put "   Set oFolder = FSO.GetFolder(oXL.DefaultFilePath)";

52         put "     For each aFile in oFolder.Files ";

53         put "       If Right(LCase(aFile.Name), 4) = "".&ext"" Then";

54         put "        oXL.Workbooks.Open(aFile.Name)";

55         put "        oXL.Visible = false";

56         put "        if (oXL.Version) >= 12 Then" ;

2                                                          The SAS System                              08:03 Friday, August 12, 2016

 

57         put "        oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) - 4) & "".xlsx"",51";

58         put "    Else";

59         put "       oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) - 4) & "".xlsx"",-4143";

60         put "   End If";

61         put " oXL.ActiveWorkBook.Close SaveChanges = True";

62         put " End If";

63         put " Next";

64         put " Set oFolder = Nothing";

65         put " end if";

66         put " FSO.DeleteFile(""&default\*.&ext""), DeleteReadOnly";

67         put " FSO.DeleteFile(""&default\*.vbs""), DeleteReadOnly";

68         put " oXL.DisplayAlerts = True";

69         put " oXL.Quit";

70         put " Set oXL = Nothing";

71         call system("&store");

72         run;

73        

74         %mend;

75         options noxsync noxwait;

76         /*%include "c:\convert.sas";*/

77         %convert_files();

 

NOTE: The file "\\&path\Report_Date.vbs" is:

      Filename=\\&path\Report_Date.vbs,

      RECFM=V,LRECL=32767,File Size (bytes)=0,

      Last Modified=12Aug2016:09:09:58,

      Create Time=12Aug2016:08:06:38

 

NOTE: 29 records were written to the file

      "\\&path\Report_Date.vbs".

      The minimum record length was 5.

      The maximum record length was 113.

NOTE: DATA statement used (Total process time):

      real time           0.02 seconds

      cpu time            0.01 seconds

     

 

78        

79         /*default is the location of the xml files, and store is the name and location(same as default) of the new xlsx file*/

80         Data _NULL_;timesleep=sleep(120,2);run;

 

NOTE: DATA statement used (Total process time):

      real time           4:00.00

      cpu time            0.03 seconds

     

 

81        

82        

83         GOPTIONS NOACCESSIBLE;

84         %LET _CLIENTTASKLABEL=;

85         %LET _CLIENTPROJECTPATH=;

86         %LET _CLIENTPROJECTNAME=;

87         %LET _SASPROGRAMFILE=;

88        

89         ;*';*";*/;quit;run;

90         ODS _ALL_ CLOSE;

91        

92        

3                                                          The SAS System                              08:03 Friday, August 12, 2016

 

93         QUIT; RUN;

94         

 

**NOTE: I removed the path and replaced it with a “fake” macro for the purposes of displaying my log and code. I also cleaned up the commented-out note in the code, which is why it looks different in the log.

Super User
Posts: 19,806

Re: Excel error - ODS tagsets.ExcelXP

There's a slightly simpler script here:

http://support.sas.com/kb/43/addl/fusion_43496_4_convert.sas.txt

 

When you run the script do you get an error? If you navigate to that script and run it you should be able to see the error in the dos window.

There's also the possibility the server doesn't allow you to run scripts as a security measure.

 

You may already know, but if you're using SAS 9.4 then you can try ODS Excel as it will create a native Excel file.

New Contributor
Posts: 3

Re: Excel error - ODS tagsets.ExcelXP

Thank you for the reply.  I do not receive an error when I run the script. 

 

I will check with my SAS system administrator for security measures.

 

It is possible that security measures are in effect, as the code only began to fail once we changed servers. Do you recommend specific language for me to use when I speak with the system admin?

 

Unfortunately, I am running 7.1 and do not have access to 9.4.

 

Do you know of an alternative method for exporting a multi-tab xml file to a multi-tab Excel workbook?

 

Besides manually converting it, of course. Haha.

Super User
Posts: 19,806

Re: Excel error - ODS tagsets.ExcelXP

Your EG is 7.1, the SAS version underlying is either 9.3 or 9.4.

 

You can check that by running the following piece of code to see your SAS version - check the log. 

 

%put &sysvlong;

 What does your code look like within tagsets?

Depending on what it is, you may have other options to create a multi sheet excel file.

New Contributor
Posts: 3

Re: Excel error - ODS tagsets.ExcelXP

Ah, it is 9.4:

 

23 %put &sysvlong;
9.04.01M3P062415

 

Thanks for that tip.

 

Here is my tagset code:

 

data data.Report_&lastmonnum.&year.;

length office_num $5.;

set final.report_total8;

 

format office_num $5.;

run;

 

ODS escapechar='#';

 

options

papersize=legal

topmargin='.8'

bottommargin='.8';

ods tagsets.excelxp file="&path/report_date.xml" style=Solutions

options(

frozen_headers = 'yes'

frozen_rowheaders = '2'

row_repeat='1-1'

column_repeat='1-2'

center_horizontal='yes'

orientation='landscape'

page_order_across ='yes'

auto_subtotals='yes'

sheet_name='Daily Detail'

default_column_width ='7'

width_fudge='0.65'

autofit_height='yes');

 

options nobyline;

 

proc report data=final.final_detail out=data.final_detail style(header)={background=PAG font_weight=bold font_face='Calibri'

font_size=12pt} style(column)={font_face='Calibri' VERTICALALIGN=MIDDLE font_size=11pt}

style(lines)={font_face='Calibri' just=left font_size=10pt} spanrows center ;

Title1 "Title";

Title2 "Subtitle";

columns obs office_num counts &columns. averages;

define obs/ computed noprint;

define office_num/ group center style(column)={background=PAG font_weight=bold font_face='Calibri'

font_size=14pt} ' ' style(header)={background=White};

define counts/ ' ' order order=internal width=20 ;

&define.;

define averages/ computed 'Average' style={tagattr='format:#,##0.0'};

compute averages;

averages=sum(&columns2.)/&numdates;

endcomp;

compute obs;

 count+1;

if not mod(count,2) then do;

call define(_row_,"style","style={backgroundcolor=#B7C5BD}");

end;

endcomp;

run;

 

ods tagsets.excelxp

options(

frozen_headers = 'yes'

row_repeat='1-1'

center_horizontal='yes'

orientation='landscape'

page_order_across ='yes'

sheet_label=" "

auto_subtotals='yes'

sheet_name='#BYVAL(Office_num)'

absolute_column_width ='6,15,6,17,11,11,11'

width_fudge='0.65'

autofit_height='yes');

 

Proc report data=data.Report_&lastmonnum.&year. out=data.reportfile_&lastmonnum.&year. style(header)={background=PAG font_weight=bold

font_face='Calibri' font_size=11pt} style(column)={font_face='Calibri' VERTICALALIGN=MIDDLE font_size=11pt}

style(lines)={font_face='Calibri' just=left font_size=10pt} center;

Title1 "Title";

Title2 "Subtitle";

Columns office_num staff_office unitnum s_full_name Ccount Icount total_incomplete;

by office_num;

define office_num/display 'Office' center style={tagattr='00'};

define staff_office/'Office Name' display center;

define unitnum/'Unit' display center style={tagattr='00'};

define s_full_name/'Staff Name' group display center;

define Ccount/"Completed in &lastmonword." analysis sum missing format=comma8.;

define Icount/"Active in &lastmonword." analysis sum missing format=comma8.;

define total_incomplete/'Total Active' analysis sum missing format=comma8.;

compute Ccount;

 count+1;

if not mod(count,2) then do;

call define(_row_,"style","style={backgroundcolor=#B7C5BD}");

end;

endcomp;

rbreak after/summarize ul ol; /*Overall total*/

compute after;

           office_num='Total';

call define(_row_,"style","style={background=PAG font_weight=bold tagattr='format:#,##'}");

note="NOTES";

note2='Notes';

note3='Notes';

space=' ';

line note $;

line note2 $;

line note3 $;

endcomp;

Footnote1 "footnote";

Footnote2 "footnote";

Run;

 

ods _all_ close;

 

Super User
Posts: 19,806

Re: Excel error - ODS tagsets.ExcelXP

You have 9.4M3 so you can try ODS Excel to create a native Excel file. I'm not sure which options are transferable and which aren't. 

 

Look for a paper by Chevell Parker from the recent SAS Global Forum that details the differences and how to use ODS Excel.

Ask a Question
Discussion stats
  • 5 replies
  • 518 views
  • 0 likes
  • 2 in conversation