topic add existing sheet in ODS and Base Reporting

add existing sheet

ja_lew — Mon, 23 Apr 2012 18:07:52 GMT

I have 2 excel workbooks each consisting of 1 sheet. and a 3rd workbook created with excelxp tagset that is roughly 20 tabs and 160+ pages (when exported to pdf)

as always the process is as automated as possible at this point and i'm looking to finish it to 100% automated. the issue is these 2 single sheet workbooks. they are created manually by another team in excel and at a high level are nothing more than a list of acronyms and a distribution template.

is there a way from within SAS to populate the workbook created by my excelxp tagset procedure with these 2 sheets?

basically as it stands today

1. run tagset report

2. open tagset report

3. open definition report

4. copy sheet1 from definitions to tagset report

5. open media report

6. copy sheet 1 from media to tagset report

what im hoping to get to:

1. run tagset report - code merges in definitions and media tab

2. profit.

ive looked @ dde and having a template workbook with a VB macro that the code would run to open/copy automatically with something like this (this is my current path i'm looking into):

data _null_;

file sas2xl;

put "[open(""&xlsTemplatePath."", 0 , true)]";

put "[run(""Macro1"")]";

put '[file.close(false)]';

put '[quit()]';

run;

Re: add existing sheet

data_null__ — Mon, 23 Apr 2012 19:20:23 GMT

You can absolutely do that using SAS to create WSH programs to do "Office Automation". You could even use DDE but I don't like it as much as writing a WSH program and calling it with SAS.

However, before you got too involved in that is itpossible/conceivable to import the two sheets in question into SAS and then recreate them in the new workbook(XML) created by tagsets.excelxp. (If that makes sense?)

Re: add existing sheet

ja_lew — Mon, 23 Apr 2012 19:24:39 GMT

it IS possible to pull the 2 sheets into sas and recreate (this was where i started) however with how dynamic the 2 sheets are it would be less efficient (as well as non-automated) with having to re-code new tweaks each week.