BookmarkSubscribeRSS Feed
ja_lew
Fluorite | Level 6

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;

2 REPLIES 2
data_null__
Jade | Level 19

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?)

ja_lew
Fluorite | Level 6

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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 823 views
  • 0 likes
  • 2 in conversation