SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Excel vba script to combine diff workbooks

Reply
Occasional Contributor
Posts: 12

Excel vba script to combine diff workbooks

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

Grand Advisor
Posts: 16,304

Re: Excel vba script to combine diff workbooks

What is your question?

Occasional Contributor
Posts: 12

Re: Excel vba script to combine diff workbooks

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.

Grand Advisor
Posts: 16,304

Re: Excel vba script to combine diff workbooks

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?

Occasional Contributor
Posts: 12

Re: Excel vba script to combine diff workbooks

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

Grand Advisor
Posts: 16,304

Re: Excel vba script to combine diff workbooks

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

Occasional Contributor
Posts: 12

Re: Excel vba script to combine diff workbooks

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.


Grand Advisor
Posts: 16,304

Re: Excel vba script to combine diff workbooks

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

Occasional Contributor
Posts: 12

Re: Excel vba script to combine diff workbooks

Hi Reeza,

In the second and third workbook will have graphs.

Respected Advisor
Posts: 3,768

Re: Excel vba script to combine diff workbooks

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.

Post a Question
Discussion Stats
  • 9 replies
  • 932 views
  • 0 likes
  • 3 in conversation