X CScript

[PSI_frank]

Greetings,

Wish to call a Visual Basic Script from a stored process. The code was developed and tested successfully on Base SAS. The script will execute when manually submitted as well. The script does not execute when called from a stored process.

*ProcessBody;

data _null_;

call symput('user',translate("&_username",'_','.'));

run;

ods tagsets.excelxp file="C:\temp\temp.xml" ;

proc print data=sashelp.class;

run;

ods tagsets.excelxp close;

options xsync ;

data _null_;

file "C:\temp\temp.vbs";

put " Dim oXL ";

put " Dim FSO";

put " Set oXL = CreateObject(""Excel.Application"")";

put " Set FSO = CreateObject(""Scripting.FileSystemObject"")";

put " oXL.DisplayAlerts = False";

put "        oXL.Workbooks.Open(""C:\temp\temp.xml"")";

put "        oXL.Visible = false";

put "        if (oXL.Version) >= 12 Then" ;

put "        oXL.ActiveWorkBook.SaveAs Left(""C:\temp\temp.xml"", Len(""C:\temp\temp.xml"") - 4) & "".xlsx"",51";

put "    Else";

put "       oXL.ActiveWorkBook.SaveAs Left(""C:\temp\temp.xml"", Len(""C:\temp\temp.xml"") - 4) & "".xls"",-4143";

put "   End If";

put " oXL.ActiveWorkBook.Close SaveChanges = True";

put " FSO.DeleteFile(""C:\temp\temp.xml""), DeleteReadOnly";

put " oXL.DisplayAlerts = True";

put " oXL.Quit";

put " Set oXL = Nothing";

run;

X "cscript C:\temp\temp.vbs";

I did test the X and echo commands using the following.

45 +x "echo hello > %sysfunc(pathname(work))\hello.dat"

45 !+ ;

46 +

47 +data _null_;

48 +

49 + infile "%sysfunc(pathname(work))\hello.dat";

50 +

51 + input; list;

52 +

53 +run;

NOTE: The infile "T:\sastemp\_TD7088_DBPDEV_\Prc3\hello.dat" is:
  Filename=T:\sastemp\_TD7088_DBPDEV_\Prc3\hello.dat,
  RECFM=V,LRECL=256,File Size (bytes)=8,
  Last Modified=05Sep2012:14:21:16,
  Create Time=05Sep2012:14:21:16

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0 

1 hello 6

NOTE: 1 record was read from the infile "T:\sastemp\_TD7088_DBPDEV_\Prc3\hello.dat".

Thanks,

F

[Yanzheng]

Hi Frank:

Your code run at PC, However, the stored procedure should run at the server side.Could you change the path of your VBS to the server path. It should be working.

[Cynthia_sas]

Hi:

  But if the server is a UNIX or Mainframe server, then running the VB Script on server side probably won't work, since Excel won't be on the server. It seems to me that this script has to run on the client machine -- where Excel will run. How is this stored process being invoked -- from which client application? For example, a stored process that uses TAGSETS.EXCELXP can only be executed from the Information Delivery Portal or the Stored Process Web Application -- and, that stored process needs to use STPSRV_HEADER -- to change the streaming CONTENT-TYPE header for the output stream. That also means a stored process that uses TAGSETS.EXCELXP cannot be executed from the SAS Add-in for Microsoft Office. If the OP searched for previous postings about using TAGSETS.EXCELXP, there would be many previous examples of using TAGSETS.EXCELXP in a stored process, including examples of altering the STPSRV_HEADER.

  Also, when you run a stored process, you generally don't use FILE= with a hardcoded file name and extension. Usually, you either use the
%STPBEGIN/%STPEND macro calls around the stored process code OR you use FILE=_WEBOUT. So, I'm having a hard time figuring out how this code would work as a stored process. What is the metadata for this stored process? How are the results defined? As streaming output, as package output? What server does this stored process run on? The Stored Process Server or the Workspace Server?

  I really think that you should work with Tech Support on this one. Tech Support can tell you whether and how (if possible) you can get your VBScript to execute.

cynthia

[PSI_frank]

Greetings All,

Thanks for the responses.

By design, the stored process is invoked through Information Delivery Portal and runs on the Workspace Server.

The only reason that I am using Visual Basic Script is that I found similar code that I could modify.  Any suggestions other than VBS?

My apologies as I did not post the entire program previously as it is functioning correctly.  Here is the remainder of the program.

filename in "C:\temp\&user&SYSPROCESSID..xlsx";

data _null_;

   rc = stpsrv_header('Content-type','application/vnd.ms-excel');

   rc =

   stpsrv_header('Content-disposition',"attachment; filename=&user&SYSPROCESSID..xlsx");

run;

data _null_;

    length data $1;

    INFILE  in  recfm=n;

    file _webout  recfm=n mod;

    input data  $char1. @@;

    put data $char1. @@;

run;

[Quentin]

Hi PSI_frank,

Can you describe the big picture of what you are trying to do? 

Is it that you are trying to get a stored procedure to write a "native" Excel file?  Looks like the current design is have stored process write a file using tagsets.ExcelXP, and then the VB script opens the file in Excel, and saves it as a native format (.xls or .xlsx)?

Didn't see an answer to whether your SAS server is running on Windows or *NIX or other?

Might ask yourself whether it is really important to have a native Excel file rather than an xml file with an .xls extension which Excel is happy to open.

That said, if you really do want a native Excel file, and cannot run the VB script on the server, you might be better off separating the process into two steps.  So stored process could run on server and return xml file(s) to the windows world.  Then you could run your vb script in Windows to convert the files.

I suppose another option would be to play with the addin.  When a stored process is run from the add-in the results come back via xml (SASREPORT format), but end up in a native Excel spreadsheet.  And I suppose if you wanted to you could use VB to script Excel to run the stored process, as in:

http://www.lexjansen.com/wuss/2011/datapresentation/Papers_Beese_T_76227.pdf

HTH,

--Q.