topic Re: Excel vba script to combine diff workbooks in Microsoft Integration with SAS

Excel vba script to combine diff workbooks

Eshan — Mon, 02 Dec 2013 20:24:09 GMT

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

Re: Excel vba script to combine diff workbooks

Reeza — Mon, 02 Dec 2013 20:49:53 GMT

What is your question?

Re: Excel vba script to combine diff workbooks

Eshan — Mon, 02 Dec 2013 21:00:23 GMT

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.

Re: Excel vba script to combine diff workbooks

Reeza — Mon, 02 Dec 2013 21:09:04 GMT

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?

Re: Excel vba script to combine diff workbooks

Eshan — Mon, 02 Dec 2013 21:22:04 GMT

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

Re: Excel vba script to combine diff workbooks

Reeza — Mon, 02 Dec 2013 21:38:13 GMT

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";

Re: Excel vba script to combine diff workbooks

Eshan — Mon, 02 Dec 2013 21:49:18 GMT

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.

Re: Excel vba script to combine diff workbooks

Reeza — Mon, 02 Dec 2013 22:20:28 GMT

Graphics/GPLOT don't export to Excel, so can you explain further what's happening in the second and third workbook.

Re: Excel vba script to combine diff workbooks

data_null__ — Mon, 02 Dec 2013 22:30:34 GMT

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.

Re: Excel vba script to combine diff workbooks

Eshan — Mon, 02 Dec 2013 22:43:41 GMT

Hi Reeza,

In the second and third workbook will have graphs.