04-07-2017 11:17 AM
04-07-2017 11:23 AM
These are legacy reports created long back , just dont want to touch the code which the report created in xls as there are 300 scripts to go through , i just want to take already created xls file and convert them to xlsx file as is ...
04-08-2017 10:22 AM
That is definitely what is needed. As long as SAS is running on a Windows machine then it should work.
The program posted by SAS on that link seems to have some bugs.
Here is a cleaned up version with the unneede extra complexity of wrapping it in a macro removed.
%let dir=c:\downloads ; %let ext=xls ; * Generate Visual Basic program ; data _null_; file "'&dir\temp.vbs'"; put "set xlapp = CreateObject(""Excel.Application"")"; put "set fso = CreateObject(""scripting.filesystemobject"")"; put "set myfolder = fso.GetFolder(""&dir"")"; put "set myfiles = myfolder.Files"; put "xlapp.DisplayAlerts = False"; 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 ""&dir.\"" & Filename & "".xlsx"", 51"; put " mybook.Close"; put " End If"; put "Next"; put "xlapp.DisplayAlerts = True"; put "xlapp.Quit"; put "Set xlapp = Nothing"; put "strScript = Wscript.ScriptFullName"; put "FSO.DeleteFile(strScript)"; run; * Show the generated program ; data _null_; infile "'&dir\temp.vbs'"; input; put _n_ 4. +1 _infile_; run; * Run the generated program ; data _null_; infile "cscript ""&dir\temp.vbs""" pipe; input; put _infile_; run;
08-17-2017 08:46 AM
Thanks a lot for this code.
However, I am using EG 5.1 and the following part of the script is not working:
infile "cscript ""&dir\temp.vbs""" pipe;
Moreover from time to time, I am getting the following error:
Unable to get the Open property of the Workbooks class
Also, is it possible to avoid to read or to import in the file list, the file temp.vbs
The script try to open that file and it stop.
Does anyone could help me with those issues?
04-07-2017 11:20 AM
If you're using the ODS EXCEL statement on 9.4 then the files should (I think) actually be XLSX files, but you gave them an xls extension. See: http://support.sas.com/documentation/cdl/en/odsug/69832/HTML/default/viewer.htm#p09n5pw9ol0897n1qe04...
Art, CEO, AnalystFinder.com
04-07-2017 02:15 PM
these reports are legacy and have been created long back in sas !
Use EXCEL VB script to modify existing files.
In 3 hours I know that I could have manually opened and done a file save as to xlsx for at least 150 files.
This is really an Excel problem since you say you do not want to change SAS code and recreate the files from scratch.