i have 3 excel workbooks and each have different sheets. I need to create a new workbook by adding one specific sheet from each workbook.
so, to, iam kinda new to vb scripting and would appreciate your help.
i Will be executing the vb script in data null using x statement in windows sas 9.2.
Eshan
What is your question?
Well, right now i am manually copying 3 sheets from 3 different workbooks and creating a new workbook.
The reason i want the vbscript is to automate the program and remove the manual manipulation in excel.
So, i need a vbscript to execute in sas so that the sas program can be completely automated.
1. Where do these worksheets come from? IE Is there a way to avoid this problem in the first place.
2. Why is SAS involved if it's solely moving around worksheets?
3. Is the VB Script already embedded in a workbook and you need to excute it or does the VB Script need to be added in to a workbook?
The worksheets are generated in for example report1.sas program by adding lots of proc tabulates and some graphs and routing that ouput into 3 diff excel workbooks.
So, now my purpose is i am trying to automate this sas job completely but i dont know how to automate the last piece where i need to create this new workbook by
copying sheets from the above workbooks
So, thats the reason i was thinking of creating a vb script which looks into the already generated 3 workbooks in the designated path and takes one sheet from each workbook and creates a new workbook with 3 sheets.
There could be multiple ways but i just thought vb script may help too. And I am planning to use this vbscript at the end of the report1.sas program.
%let vbs = ""path\sample.vbs"";
Data _null_;
x "&vbs";
run;
Eshan
Best solution: reorder your project output, if necessary look into GREPLAY to control when your output goes to Excel so it gets combined straight out of excel
IF the VBS script has all the parameters you don't need a data _null_ step, simply :
%sysexec "&vbs";
i do need the 3 workbooks and i also need the new workbook with all the sheets from the 3 workbooks so i am not quiet sure how to route the output in to these final various excel reports.
1st workbook usess 5 proc tabulates and routes to workbook1.xls with sheet = sheet1
2nd workbook uses one proc gplot and routes to workbook2.xls with sheet = sheet2
3rd workbook uses one proc gplot and routes to workbook3.xls with sheet = sheet3
So now i also need a new workbook(workbook_final) with sheet1, sheet2 and sheet3 from above workbooks and i am kindaa lost here to achieve this.
Graphics/GPLOT don't export to Excel, so can you explain further what's happening in the second and third workbook.
Hi Reeza,
In the second and third workbook will have graphs.
VBScript is not VBA and is not "containted" in the workbook.
VBScript - Wikipedia, the free encyclopedia
https://communities.sas.com/message/128928#128928
https://communities.sas.com/message/124373#124373
I have posted other examples. If you search for CSCRIPT you find more examples.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!