BookmarkSubscribeRSS Feed
Deepti44
Fluorite | Level 6

I have around 300 xls reports getting created through ODS, how can i convert them to xlsx files  !

10 REPLIES 10
Jagadishkatam
Amethyst | Level 16
While you are creating the xls files through ODS using the file extension of xls, you coud also create the excel files with extension xlsx

could you please let us know the program you are using to create the xls files
Thanks,
Jag
Deepti44
Fluorite | Level 6

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 ...

Jagadishkatam
Amethyst | Level 16
I hope this link will help you

http://support.sas.com/kb/43/496.html

please try the macro in the link
Thanks,
Jag
Tom
Super User Tom
Super User

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;
alepage
Barite | Level 11

Thanks a lot for this code.

 

However, I am using EG 5.1 and the following part of the script is not working:

 

data _null_;
  infile "cscript ""&dir\temp.vbs""" pipe;
  input;
  put _infile_;
run;

 

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?

Regards,

 

Alain

 

 

ballardw
Super User

Excel VB programming is your best bet.

Or go back to the SAS process that created them and change things to create XLSX instead of XLS.

Deepti44
Fluorite | Level 6

there are 300 scripts to go through and ithese are old legacy scripts created in sas ods.

art297
Opal | Level 21

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

 

Deepti44
Fluorite | Level 6

these reports are legacy and have been created long back in sas !

ballardw
Super User

@Deepti44 wrote:

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 4962 views
  • 2 likes
  • 6 in conversation