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

stuart_snap — Mon, 17 Feb 2014 20:06:46 GMT

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!

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

Cynthia_sas — Tue, 18 Feb 2014 03:34:06 GMT

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

topic XML to XLS script not working... any suggestions? in ODS and Base Reporting

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

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