cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
eferencik
‎11-12-2020
  • 9 Posts
  • 0 Likes Given
  • 0 Solutions
  • 2 Likes Received

Re: Excel File Conversion not Working

by Fluorite | Level 6 in ODS and Base Reporting
‎08-30-2018 04:38 PM
‎08-30-2018 04:38 PM
I need to correct one piece of information I provided earlier.  Both machines are running Windows 7 Professional. Here are the answers to your questions.  Any tips or suggestions are greatly appreciated.   Is the file being created ever, Yes/No?  Yes.  When I manually run or schedule the program on my computer the file is created/converted without issue.  When the program is manually run on the production virtual machine the file is created without issue.  When the program is scheduled through the task scheduler on the virtual machine, the file is not converted and the .vbs file/script is left behind.  When we double click the .vbs it converts to the .xlsx file we need, but that does not work for programs that are automated to run and email to a distribution list. Is the file being created consistently?  The .xls is being created consistently.  The file is converted on my machine to .xlsx consistently. Are you trying to create both an XLSX and XLS file?   The program first outputs the report as .xls using ODS TAGSETS.EXCELXP and then is supposed to convert that file to a .xlsx. Why is there an XLS file involved in this process at all? Is that the XML output from ODS TAGSETS.EXCELPXP you're referring to?  That is how we initially set up the reports.  I don't believe that is the issue since my machine can run the program without issue from the task scheduler. Are you creating the VBS script each time, or just running/calling it each time?  The VBS script is created each time because it is specific to the report that is run, and the file name contains unique information (i.e. month, year, date) so each time the program runs the resulting file name is different. What happens if you call the VBS script from a command line or SAS, not in Batch?  Again, the only time there is an issue is when the program is run from the task scheduler on our virtual machine that runs our production reports. ... View more

Re: Excel File Conversion not Working

by Fluorite | Level 6 in ODS and Base Reporting
‎08-30-2018 11:50 AM
‎08-30-2018 11:50 AM
We have tested the program several times on the production machine.  One of those times the .xlsx was created but that was 2 minutes after the program finished running so the email was not sent out.  Every other time we tested the program the .xls was produced as expected, but instead of the .xlsx there was a temp.vbs file.   I will looking into the security protocol, but with the conversion working the 1 time (granted 2 minutes later) I don't think that the security protocol is the issue. ... View more

Re: Excel File Conversion not Working

by Fluorite | Level 6 in ODS and Base Reporting
‎08-30-2018 08:29 AM
‎08-30-2018 08:29 AM
We have a couple hundred programs that would have to be modified and converting ODS TAGSETS.EXCELXP to ODS EXCEL is not a quick process.  It would require more time than we have to update all of those programs with ODS EXCEL and have the output formatting the way we currently have it.  Copying and pasting logic into each program to call the conversion program is faster and maintains the desired formatting. ... View more

Excel File Conversion not Working

by Fluorite | Level 6 in ODS and Base Reporting
‎08-29-2018 04:02 PM
‎08-29-2018 04:02 PM
I have create a macro program, that converts .xls files to .xlsx files, based on what I found at this link http://support.sas.com/kb/43/496.html.  I call the program in other programs to allow me to produce reports formatted with ODS TAGSETS.EXCELXP and convert the output to .xlsx before emailing the .xlsx version to a distribution list.    When I manually run a program that calls this macro program on my computer it works.  When I schedule it through the task scheduler on my computer it works.  When we schedule it to run on our production machine (VM) it does not convert to .xlsx and the email logic cannot find the file that should be attached.  My computer runs Windows 10 while the production machine runs Windows 7.  Due to issues with the task scheduler in Windows 10 we cannot upgrade the VM from Windows 7 to Windows 10.    How do I get SAS to wait for the file conversion to be complete? ... View more

Convert ODS TAGSETS.EXCELXP .xls output to .xlsx file

by Fluorite | Level 6 in ODS and Base Reporting
‎07-23-2018 12:20 PM
‎07-23-2018 12:20 PM
I attempted using the code shown here, http://support.sas.com/kb/43/496.html, but need something that will convert only the report that the program outputs.  I do not want every .xls file in the folder converted this code.   How would I modify the macro program to only convert the report name specified?   options noxwait noxsync; %macro convert_files(default=,ext=); data _null_; file "'&default\temp.vbs'"; put "set xlapp = CreateObject(""Excel.Application"")"; put "set fso = CreateObject(""scripting.filesystemobject"")"; put "set myfolder = fso.GetFolder(""&default"")"; put "set myfiles = myfolder.Files"; put "xlapp.DisplayAlerts = False"; put " "; put "for each f in myfiles"; put " ExtName = fso.GetExtensionName(f)"; put " Filename= fso.GetBaseName(f)"; put " if ExtName=""&ext"" then"; put " set mybook = xlapp.Workbooks.Open(f.Path)"; put " xlapp.Visible = false"; put " mybook.SaveAs ""&default.\"" & Filename & "".xlsx"", 51"; put " End If"; put " Next"; put " mybook.Close"; put " xlapp.DisplayAlerts = True"; /* Removes original files */ /*put " FSO.DeleteFile(""&default\*.&ext""), DeleteReadOnly";*/ put " xlapp.Quit"; put " Set xlapp = Nothing"; put " strScript = Wscript.ScriptFullName"; put " FSO.DeleteFile(strScript)"; run; x "cscript ""&default\temp.vbs"""; %mend;   I want to be able to specify the file that needs to be converted.  Something like the code below where the report name is "CO Turnover Rate &title".   options noxsync noxwait; %include "c:\convert.sas"; %convert_files(default=c:\users\desktop\Test\CO Turnover Rate &title,ext=xls);   ... View more

Re: ods tagsets.excelxp title row height issue

by Fluorite | Level 6 in ODS and Base Reporting
‎07-20-2018 03:50 PM
‎07-20-2018 03:50 PM
How would I format that to have the formatting specified in my current title statements? ... View more

ods tagsets.excelxp title row height issue

by Fluorite | Level 6 in ODS and Base Reporting
‎07-20-2018 03:13 PM
‎07-20-2018 03:13 PM
  I am having issues with the report's title row heights not being consistent.  Here is my ods statement.   ODS LISTING CLOSE; ODS NORESULTS; OPTIONS LeftMargin = .5in RightMargin = .5in TopMargin = .5in BottomMargin = .5in missing=' '; ods tagsets.excelxp file="c:\CO Turnover Rate &title..xls" style=minimal OPTIONS (Orientation = 'landscape' width_fudge='0.6' absolute_COLUMN_WIDTH="12,10,10,10" fittopage='yes' Center_Horizontal='on' EMBEDDED_TITLES='yes' EMBEDDED_FOOTNOTES='yes' autofit_height='on' Pages_FitWidth='1' Pages_FitHeight='100' row_repeat='header');   ods tagsets.ExcelXP options(sheet_name="CO Turnover"); PROC REPORT DATA=FINAL NOWINDOWS MISSING style(header)=[font_face=Times font_weight=bold background=ltgray] style(column)=[font_face=Times]; COLUMNS DATE MON n EMPS TERMS PCT; DEFINE DATE / ORDER=DATA NOPRINT; DEFINE MON / DISPLAY 'Month'; DEFINE EMPS / ANALYSIS 'CO Count*' STYLE(COLUMN)=[TAGATTR="FORMAT:#,###"]; DEFINE TERMS / ANALYSIS 'Terminations' STYLE(COLUMN)=[TAGATTR="FORMAT:#,###"]; DEFINE PCT / ANALYSIS 'Turnover Rate' STYLE(COLUMN)=[TAGATTR="FORMAT:0.0#%"]; DEFINE N / NOPRINT; COMPUTE AFTER; DATE="&title"; EMPS.SUM=EMPS.SUM/N; TERMS.SUM=TERMS.SUM; PCT.SUM=TERMS.SUM/EMPS.SUM; ENDCOMP; rbreak after / dol skip summarize style(summary)=[background=ltgray font_weight=bold]; TITLE JUSTIFY=CENTER bold height=14pt font=times "Correctional Officer Turnover Rate"; title2 JUSTIFY=CENTER bold height=14pt font=times "&title"; title3 JUSTIFY=CENTER italic height=12pt font=times '(includes Job Class codes JD30, JD35, JD40 & JD50)';   RUN;   ODS TAGSETS.EXCELXP CLOSE; ODS LISTING; ODS RESULTS;   Here is the title and column header portion of my output.  I don't understand why rows 1 and 3 are taller than row 2.   ... View more

Re: Copy Excel Workbook and Save Under New Name

by Fluorite | Level 6 in SAS Programming
‎02-14-2018 01:12 PM
2 Likes
‎02-14-2018 01:12 PM
2 Likes
Thank you.  I searched FCOPY, found this and it works.   filename src "path-to-src" recfm=n; filename dst "path-to-dst" recfm=n; %let rc = %sysfunc(FCOPY(src,dst)); %put %sysfunc(SYSMSG()); ... View more

Copy Excel Workbook and Save Under New Name

by Fluorite | Level 6 in SAS Programming
‎02-14-2018 12:50 PM
‎02-14-2018 12:50 PM
I am trying to use SAS to copy an existing excel workbook (a template) and save it with a new name in a different folder (once updated with current data).  I have tried both steps below, but I cannot get either to work.  It seems like it should be simple to do, the only difference between the 2 workbooks would be the file name and location.   First tried: options noxwait; data _null_; rc= system("copy S:\sas\Regular Reports\Vacancy Template.xlsx S:\oim\Regular Reports\Vacancy Report &sysdate..xlsx");   put rc=; run;   Then I tried: options noxwait; data _null_; oldname="S:\sas\Regular Reports\Vacancy Template.xlsx"; newname="S:\oim\Regular Reports\Vacancy Report &sysdate..xlsx"; rc= system(quote(catx(' ','copy',quote(trim(oldname)),quote(trim(newname))))); put rc=; run; ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎11-12-2020 07:38 PM
Likes from
View All
Quick links