BookmarkSubscribeRSS Feed
Michael_W
Calcite | Level 5
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?
5 REPLIES 5
Vince_SAS
Rhodochrosite | Level 12


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
Calcite | Level 5
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
Calcite | Level 5
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
Rhodochrosite | Level 12
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
Calcite | Level 5
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

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

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
  • 5 replies
  • 1248 views
  • 0 likes
  • 2 in conversation