12-02-2013 03:24 PM
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.
12-02-2013 04:00 PM
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.
12-02-2013 04:09 PM
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?
12-02-2013 04:22 PM
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"";
12-02-2013 04:38 PM
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 :
12-02-2013 04:49 PM
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.
12-02-2013 05:30 PM