Invoking a Word Macro from a Stored Process within AMO for Word

[Michael_W]

I think this is a longshot, but does anyone know if there is a way to invoke a Word VB macro from a stored process that is run from AMO?

Here's my challenge ...

I have developed this stored process that creates an very slick RTF report (i.e. multiple tables and graphs with Excel-like formatting). The SP outputs the RTF document to a specific location with a content and time-specific file name. The problem is that report needs to include our company logo in the header which also contains several header lines. I've tried everything I could think of to include the logo using code, but to no avail. However, I was able to create VB macro in word that adds the logos to each page/section of the report very quickly.

What I am hoping is that there is a way for a stored process that is run from AMO to trigger the VB macro to insert the RTF file into the document and launch the logo-adding macro! Because I am using RTF control words, INSERTing the file into a blank Word document containing the VB macro was the only way I could get it to produce exactly what I need.

Any ideas?

[Vince_SAS]

Can you provide some more details about how you are creating the RTF file? Which SAS procedures are you using, and what have you tried that hasn't worked in getting the logo into the header?

You say that the RTF file is written out to a static file with a specific name. Under this scenario, the role of AMO is unclear to me.

Note that the RTF spec may not support VBA macros. I created a Word 2003 document containing a macro, and when I attempted to save it as an RTF file, I got this warning:

[pre]All macros in this document will be lost if the document is saved in
Rich Text Format. Do you want to save in this format anyway?[/pre]

Vince DelGobbo


SAS R&D

[Michael_W]

Hi Vinnie,

Thanks for responding. My short-and-sweet response is that I believe I have discovered a work-around.

Details: If I save the VB macro to default Word template (normal.dot) the macro that I have built to add the logos to my headers is available to any document that I open with Word; including the RTF file that I am creating with my stored process. Because AMO for Word is not configured correctly in our environment, I have transitioned from Word/AMO to Excel/AMO which I essentially use the vehicle to launch my stored process .., my stored process streams back to the app a brief message and a hyperlink pointing to the RTF file that is also created by the stored process via ODS. When user clicks on the hyperlink it will open the RTF file in a Word session and then they will run the VB macro to add the logos.

As far as your specific questions go ...

1. The RTF file is created with ODS using PROC Report and PROC GChart
1B. I've tried many things, but the main hurdle is splitting the header into two columns, merging the right-hand column, and inserting the logo into the merged cells in the right-hand column. Easy to do in VB.

2. Explained above.

3. I was hoping there was a way to stream imbedded VB macro code from SAS to my Word session such that it would automatically insert the RTF file and call the macro to add the logos ...I think my work-around will do the trick though!

[Michael_W]

Sorry, I mean to address you as Vince.

I have oen additional question ...

Here's my situation: I am running a stored process from Excel AMO and I want the results that are streamed to EXCEL to resize the column to a specific width. My results are generated via PROC REPORT.

Thanks again!

[Vince_SAS]

For the header, is this what you want?

[pre]
First line of header here image spans
Second line of header here both rows
[/pre]

If so, I'll try to find out if it is possible.

It's been a long time since I used VBA regularly, but I believe you can set up the Word macro to run every time a document is open. I mention this because an alternate vehicle for executing the stored process is the SAS Stored Process Web Application:

http://support.sas.com/documentation/cdl/en/stpug/61271/HTML/default/a003152812.htm

Instead of writing the RTF files to disk, generate them dynamically and direct the output back to the Web browser. When the document is opened, your macro can automatically execute without user intervention. This may be undesirable because the macro will run for all documents, not just yours. However, you can probably write the macro + RTF output in such a way that the macro exits without doing anything if it is not one of your documents.

Resizing columns in Excel is always tricky. I don't know if AMO provides any special resizing functionality, so I suggest posting your question to the Microsoft Office Integration forum:

http://support.sas.com/forums/forum.jspa?forumID=7

I have written a number of papers on using the ExcelXP tagset, including resizing columns, but this type of output is not supported by AMO. It is, however, supported by the SAS Stored Process Web Application.

If you are attending SAS Global Forum next week, I suggest you bring your code and data on a USB drive, and stop by the ODS and Stored Process booths in the SAS Demo Room. I will be presenting a hands-on workshop on the ExcelXP tagset on Monday afternoon at 4:00 pm.

Vince DelGobbo
SAS R&D

[Michael_W]

Vince, thank you again for all of your excellent support. I think my current solution, albeit not perfect, will meet my customer's needs and at the same time minimize the amount of additional development time that is required. The re-sizing of Excel columns was more of "nice-to-have" if it was something simple to code.

Since my VB macro does exactly what I need it to do, all I have to do is figure out how to share my macro with the handful of users that will be running the process.

That being said, I am sure that your suggestions and papers will be relied upon heavily in the future!

Thanks again, Michael