How to run vb script file from SAS Unix\Aix server

Reply
Contributor
Posts: 34

How to run vb script file from SAS Unix\Aix server

I've a Vbscript for merging excel sheet into a single workbook. I would like to know whether we could execute vbscript (.vbs) file in unix\aix file system on server.



Contents of test.vbs file is as below:
Dim xlapp, xlwkb,sourcefile, targetfile

set xlapp=createobject('excel.application")

sourcefile="C:\Users\Desktop\test\demo.xlsx"

set xlwkb =xlapp.workbooks.open(sourcefile)

targetfile="C:\Users\Desktop\test\final.xlsx"

xlwkb.close


Is it possible to run the above vbs file from SAS Unix\aix server?


If yes, please help me with the procedures.

vb

Thanks in advance.

Bhoopesh

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: How to run vb script file from SAS Unix\Aix server

Excel is not on Unix (it is a Windows application), therefore you cannot create an Object of type Excel.  Why do you not just read the base file in, do your merging/comparisons, then export again? 

Contributor
Posts: 34

Re: How to run vb script file from SAS Unix\Aix server

Hi RWA9,

Thanks for your response

Yes we can do a export of final step . But one of my friend has done all the excel formatting and pivot tables creation using the vb macro.

So we need to call the vb macro from the sas finally.

Below is just an example...


Dim xlapp, xlwkb,sourcefile, targetfile

set xlapp=createobject('excel.application")

sourcefile="C:\Users\Desktop\test\demo.xlsx"

set xlwkb =xlapp.workbooks.open(sourcefile)

targetfile="C:\Users\Desktop\test\final.xlsx"

xlwkb.close



We need to call vb macro at the end via SAS.?

Is it possible..


I have read about the following code using dde:



filename sas2xl dde 'excel|system';

/* This data step opens the file with the data and runs */ /* the Excel macro to create the pivot table of the data */

data _null_;

file sas2xl; put "[open(""&xlsdir.\VASUG Wireless.xlsx"", 0 , false)]";

rc = sleep(2);

put "[run(""'Main Wireless Macro for SAS.xlsm'!Create_wireless_pivottable"")]";

put '[file.close(true)]'; run;


But this is not working and the filename sas2xl dde 'excel|system'; is giving error while i am runnig it on Unix server.

Please suggest

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: How to run vb script file from SAS Unix\Aix server

It will not matter if you use DDE, proc export or anything else, MS Office/VB scripting is not, in any part, available on UNIX, which is a completely different operating system to Windows.  If someone has created an Excel document that you need to put data into, then I would suggest the best method (other than deleting Excel and using proper reporting software) is to export your data as CSV (plain text, delimited), and put that somewhere where the Excel file can access it.  Then when you open the Excel file have a VBA macro which reads the data from the CSV. 

Esteemed Advisor
Posts: 6,646

Re: How to run vb script file from SAS Unix\Aix server

Visual Basic is only supported on Windows, so the basic answer is no. Apart from the fact that the target for your script (Excel) is also not available outside of Windows.

You could try to open a remote connection (ssh) to the/a windows machine to run the script remotely there, but why would you do that? It's much easier to set up the data in SAS and export as a final step (If that export is needed at all, I have not come across a BI problem that could only be solved in Excel)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 34

Re: How to run vb script file from SAS Unix\Aix server

Hi Kurt,

Could you please provide an exmaple of open a remote connection (ssh) to the/a windows machine to run the script remotely there

Thanks,

Bhoopesh

Esteemed Advisor
Posts: 6,646

Re: How to run vb script file from SAS Unix\Aix server

I don't do this, so I have no example, just a hint how to execute Windows commands from a non-Windows platform. You need a secure shell server program (ie freeSSHd) on Windows, so you can log in to the Windows machine and execute the script from the commandline, which VB should be able to do.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 6 replies
  • 1093 views
  • 1 like
  • 3 in conversation