The SAS Output Delivery System and reporting techniques

XML to XLS script not working... any suggestions?

Reply
Contributor
Posts: 24

XML to XLS script not working... any suggestions?

Hi.

I found code on the internet to change an xml file to a xls file using a VBS Script.  Seems easy enough, but for some reason, it won't actually execute the VBS.  The code below creates the VBS code, executes it (supposedly), deletes the VBS, and deletes the original xml file.  The VBS writes perfectly and will execute if I double click the file or run in it a cmd window.  But, SAS skips over it.  It does successfully deletes both the VBS and the xml.

Does anyone see any reason why this wouldn't work?

DATA _NULL_;

FILE '\\fileserver1\Users\S\Snap Rap\2013\SnapRap_wf_today.vbs';

PUT 'Set objExcel = CreateObject("Excel.Application")';

PUT 'objExcel.Visible = FALSE';

PUT 'objExcel.DisplayAlerts = FALSE';

PUT 'Set objWorkbook = objExcel.Workbooks.Open("\\fileserver1\Users\S\Snap Rap\2013\SnapRap_wf_today.xml")';

PUT 'objExcel.ActiveWorkbook.SaveAs"\\fileserver1\Users\S\Snap Rap\2013\SnapRap_wf_today.xlsx",51';

PUT 'objExcel.ActiveWorkbook.Close';

PUT 'objExcel.Quit';

RUN;

X "\\fileserver1\Users\S\Snap Rap\2013\SnapRap_wf_today.vbs"; * This won't execute *;

X del "\\fileserver1\Users\S\Snap Rap\2013\SnapRap_wf_today.vbs"; * Delete VB Script - this works fine! *;

X del "\\fileserver1\Users\S\SnapRap\2013\SnapRap_wf_today.xml"; * Delete XML File - this works fine! *;

Thanks!

SAS Super FREQ
Posts: 8,743

Re: XML to XLS script not working... any suggestions?


Hi:

  You might want to work with Tech Support on this. I thought that this had to be done with a DDE type of communication scenario, where both SAS and Excel had to be open in order for you to send the commands. Or else, you had to use .NET to make this type of thing work. Tech Support is probably your best bet for a resolution.

cynthia

Ask a Question
Discussion stats
  • 1 reply
  • 403 views
  • 0 likes
  • 2 in conversation