BookmarkSubscribeRSS Feed
Eshan
Calcite | Level 5

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

9 REPLIES 9
Reeza
Super User

What is your question?

Eshan
Calcite | Level 5

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.

Reeza
Super User

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?

Eshan
Calcite | Level 5

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

Reeza
Super User

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

Eshan
Calcite | Level 5

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.


Reeza
Super User

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

Eshan
Calcite | Level 5

Hi Reeza,

In the second and third workbook will have graphs.

data_null__
Jade | Level 19

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 9 replies
  • 2147 views
  • 0 likes
  • 3 in conversation