DATA Step, Macro, Functions and more

Creating 1000+ versions of an Excel workbook

Reply
Super Contributor
Posts: 376

Creating 1000+ versions of an Excel workbook

Hi,

Not sure which discussion board to post this in???  Hopefully this is a good spot.  This post is also related to

I have a dataset with 1000+ provider (hospital) numbers and matching hospital name.  I need to create an Excel workbook named like <hospital_number>_<hospital_name>.xlsm.  So this will be 1000+ workbooks.

I also need to set a data cell in each copy of the workbook to the value of the hospital number.

Say we use SASHELP.CLASS as a proof of concept.  Say I create the template workbook as template.xlsm.  Say I want to create Alfred_M.xlsm, Alice_F.xlsm, etc (NAME_SEX.xlsm).  And say I want to set Sheet1, $B$1 = <name>.

Can you suggest an approach?  Although I've rarely used DDE, perhaps that could be used?  Or, I could load SASHELP.CLASS into a worksheet, then write a VBA macro to loop over the rows, set the cell in template.xlsm, then Save As <name>_<sex>.xlsm.  I'll have to hit the VBA books for that approach.  Or perhaps there's a better approach?

Thanks,

Scott

Super User
Posts: 5,257

Re: Creating 1000+ versions of an Excel workbook

I'll suggest to use call execute (from a data _null_ step reading your provider table) to a macro that creates each workbook with the necessary contents.

Data never sleeps
SAS Super FREQ
Posts: 8,743

Re: Creating 1000+ versions of an Excel workbook

Hi:

  I do not believe the CALL EXECUTE method will work when using the SAS Add-in for Microsoft Office (AMO). Since the OP said that this issue was related to his post in the Stored Process forum, there are some ways that AMO works that will not be the same as Base SAS.

  1) When you use AMO, you do not really have a way to create "files", as with ODS or DATA _NULL_ - from inside an Office application, such as Excel or PPT or Word, you can do a File-->Save AS; or there may be VBA/automation methods, but SAS is running on a server and generally, the folks using AMO are returning results to their machines and the server won't write directly to their machines. Possibly in EG, a program could work with call execute, but I'm not sure.

  2) The requirement to make a .XLSM file -- as far as I know, you can only create XLSM files with the Libname Engine for Excel. You'd have to check with Tech Support to see whether this would work from a stored process. I don't think it will. The only results that Excel/AMO can receive from a stored process are SASReport XML, HTML and CSV. The SASReport XML is transformed into proprietary Excel format, but I believe that format is NOT .XLSM format.

  3) Without using named ranges and the LIBNAME engine, there is no way to write directly to a cell in a worksheet or workbook, like $B$1. In Excel, you can run a stored process and return the SP results to a direct cell as a place to start writing, but you cannot have a stored process create 1000+ workbooks in one fell swoop.

  4) I don't think you can use DDE with the Platform. SAS runs on the server and the instance of Excel where your AMO runs is on a different machine. For DDE to work, an open instance of SAS has to communicate with an open instance of Excel. VBA is probably a better choice, as illustrated by the paper you referenced in the other posting.

  5) I think that this question, if related to the stored process posting, is probably better posted in the stored process forum. AMO and Excel and SAS BI Platform do not work the same way as SAS and Excel in a simpler scenario.

  6) Although I could write the macro you envision in Base SAS, using ODS or the LIBNAME engine (the one that creates ALICE_F.xls and ALFRED_M.xls), I don't know enough about XLSM files to write something that creates valid XLSM files. And, even if I did write that macro to get the report name right, I could not, in the same program guarantee that $B$1 contained the specific information you wanted.

Overall, my recommendations would be to open a track with Tech Support and/or post your question, in the Stored Process Forum.

cynthia

Super User
Posts: 17,840

Re: Creating 1000+ versions of an Excel workbook

Working through this right now, though I only need several hundred not thousands. The process is still the same though, I think and is Cynthia's 2/3 suggestion.

Here's an approach I'm using, which doesn't use AMO or a stored process. If you're on a server I'm not sure this would work either.

1. Create the template file in Excel with a named range to populate the data required.

Write a macro loop to:

2. Use the %sysexec command to copy the excel file (.xlsm) and rename it appropriately

3. Populate the named range with the values required using the libname engine

4. Open file with DDE and save as PDF in my case - still working on this part.

Good Luck!!!

SAS Super FREQ
Posts: 8,743

Re: Creating 1000+ versions of an Excel workbook

Hi:

  This is a great suggestion, but it won't work with Stored Processes and AMO using the BI Platform.

--I don't think you can use X commands or SYSEXEC

--AMO does not "technically" use the LIBNAME engine

--you can't use DDE on the BI Platform.

cynthia

Super Contributor
Posts: 376

Re: Creating 1000+ versions of an Excel workbook

Hi All,

Thanks for the replies so far...

I can see now that my reference to the other posting was a red herring and only confused the issue.  It was mentioned merely to set context.  So forget that reference; let me restate the problem.

* I have a data set containing records and variables.  In the case of SASHELP.CLASS, I have 19 records and two variables of interest, NAME and SEX.

* I have a template Excel file, template.xlsm.

* I want to create 19 copies of this template Excel file, named according to the data in my SAS data set.  If this was all I wanted to do, the problem would be trivial.  For example, I could easily generate a simple batch file with "copy template.xlsm <name>_<sex>.xlsm" statements, and then just execute it.

* However, I also want to set a particular cell in the copied Excel file to a particular value.  In the case of SASHELP.CLASS, it is the value of NAME.  This is my sticking point.

I should also point out, while I have full access to DMS, EG, etc, the end user only has access to EG.  So, DDE may not be a viable option.  I could generate the output files for her, but I would prefer she be self-sufficient.

So, in summary:

* Given a template Excel file (heck for a POC it can just be an empty file), can I set a particular cell to the value of a SAS variable?  Using EG or VBA?

* If not, then same question, without the restriction that it be EG.

* If VBA is my only option then just let me know.  I can export the data set to either a file or Excel worksheet, then write VBA to spin through that data and create the output workbooks (with a lot of referencing to VBA books/websites!)

I posted here because AFAIK DDE falls under Base programming.

Apologies for the confusing initial message.

Thanks,

Scott

Super User
Posts: 17,840

Re: Creating 1000+ versions of an Excel workbook

Scott, if you set the cells you want to be populated as named ranges in the template you can simply write a value to those cells using a proc export. You shouldn't need VBA. If your EG can 'reach' the file you'll be able to export directly to those cells. But to repeat myself it must be a named range, and since its a predefined template that should be easy enough.

Excel 2007/2010 Tutorials - Named Ranges

SAS Super FREQ
Posts: 8,743

Re: Creating 1000+ versions of an Excel workbook

Hi:

  If you have SAS on a server and Excel on a local machine, it is unlikely that DDE will work in that scenario. Both SAS and Excel have to be open at the same time for a DDE conversation to take place. Especially if you have EG in the mix, I do not believe that with EG, SAS and DDE, you could make this work -- this would be a question for Tech Support.

If you have a named range in your worksheet/workbook, then you could use the LIBNAME engine to write to a named range. Again, with SAS on a server and Excel on a different machine, you would have to use the form of the LIBNAME engine with the PORT= and SERVER= options specified, using SAS/Access to PC File Formats or the PC File Server, depending on your version of SAS and the "bitness" of SAS and Windows (32 v 64).

EG communicates with Excel using either the Microsoft JET or the Microsoft ACE engine (not with the SAS Libname engine). I do not know whether you can write to one cell or to an xlsm file using the JET or ACE engine. This would also be a question for Tech Support.

I believe there are limitations on the creation of .XLSM files...would suggest you search Tech Support for more notes like these:

39325 - The error "Database or object is read-only" occurs when you deport data to Microsoft Excel 2...

33228 - An error occurs when you use SAS® 9.2 to import or export Microsoft Excel or Access files in...

When you say that you are "setting context" with the other question (that had to do with Stored Processes), it is directly relevant whether you are going to try to do all this workbook creation in the context of the SAS Add-in for Microsoft Office. But, it seems to me that your question is so specific that it will come down to configuration and version information and you might be better served by working with Tech Support.

cynthia

Super Contributor
Posts: 376

Re: Creating 1000+ versions of an Excel workbook

Hi,

Thanks all.  We have SAS and Excel on the server machine.  Also EG but that's irrelevant.  We have local SAS (so DMS), EG, and Excel on the local machine.  We have SAS/Access to PC File Formats on the local machine only.  Weird I know, it came down to dollars in licenses fees for SAS/Access to PC File Formats on the server.

So, we do have Excel on the server, but the end user is using local EG connecting to SAS on the server.  I'm not sure if EG can use DDE at all, regardless of whether Excel is on the server or not.  I've *never* used DDE.

The other post mentioned generating a dynamic where clause using cells in an Excel workbook.  The purpose of this post is to intialize the values of those cells for the thousand workbooks.  That's the only relevance to the other post.

If we limit the scope of this question to "how do I set the value of a cell in Excel to the value of a variable in a SAS dataset", I see three approaches so far:

* DDE

* PROC EXPORT to a named range.  I didn't know about that one - thanks Reeza -  but I assume I need SAPCFF on the server.

* Export the SAS data to Excel and use VBA

I'm thinking VBA may be the way to go in this scenario.

Scott

SAS Super FREQ
Posts: 8,743

Re: Creating 1000+ versions of an Excel workbook

Hi:

In addition to DDE, there are possibly also ODBC and/or OLE-DB approaches.

PROC EXPORT -- whether you need SAS/Access to PC File Formats or the PC File Server will be determined by your version of Windows, your version of SAS and your "bitness" of both SAS and Excel. (For example, for 64 bit SAS to talk to 32 bit Excel in SAS 9.3, you need the PC File Server.) For 64 bit SAS to talk to 64 bit Excel, you do not need the PC File Server. For Unix SAS to talk to Windows Excel, you need the PC File Server. That's why I recommended talking to Tech Support, the nuts and bolts of versions and bitness will determine what you need.

Is the requirement for B1 hard and fast? For example, if you used ODS, you could get your "extra" information into A1 using the code example below and the embedded_titles suboption.

cynthia

options nobyline;

ods tagsets.excelxp file="c:\temp\alice_f.xml" style=htmlblue

    options(embedded_titles='yes');

      

proc report data=sashelp.class nowd;

  by name;

  title j=l 'Name is: #Byval1';

  where name = "Alice";

  column age sex height weight;

run;

ods tagsets.excelxp close;

options byline;

Super Contributor
Posts: 376

Re: Creating 1000+ versions of an Excel workbook

I don't think I can use ODS tagsets to derive the workbook "from scratch" since it wouldn't contain all the VBA code in the target workbook.  This is why I think I need to copy a template workbook.

Ask a Question
Discussion stats
  • 10 replies
  • 464 views
  • 0 likes
  • 4 in conversation