topic Re: How do I send a VB command to Word that calls a Macro that requires a v in Microsoft Integration with SAS

How do I send a VB command to Word that calls a Macro that requires a value

deleted_user — Wed, 07 Jan 2009 16:18:57 GMT

I have a SAS Macro that constructs VB commands to send to Word, but I'm stuck on trying to call a Word Macro that requires a value passed to it (such as: call prt_pgs_rng ("1-10"). I keep getting a Word Compile error.

Any thoughts?

Re: How do I send a VB command to Word that calls a Macro that requires a value

deleted_user — Wed, 07 Jan 2009 20:19:22 GMT

more details please! are you invoking the Word Macro from SAS or within Word? If it's the former case, are you using DDE? listing the code would be helpful too.

Re: How do I send a VB command to Word that calls a Macro that requires a value

deleted_user — Thu, 08 Jan 2009 20:20:09 GMT

I'm using DDE to send VB commands to Word to print all or selected pages of the current Word Document. If I invoke a Word Macro that does not require any values passed to it, it works fine. But, if the Word Macro requires a value to be passed to it (such as the range of pages to be printed) it fails.

These are the two Word Macros that I'm working with:

Sub prt_pgs_all()
' Print All Pages in Current Document
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0
End Sub

Sub prt_pgs_rng(stPg As String)
' Print a Range of Pages in Current Document
' "1-1" = Print Page 1
' "1-10" = Print First 10 pages
Application.PrintOut FileName:="", Range:=wdPrintRangeOfPages, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:=stPg, PageType:=wdPrintAllPages, _
ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0
End Sub

To print all pages I send the following comand:
put '[call prt_pgs_all]'; - this works fine
To print only the first page I send the following command:
put '[call prt_pgs_rng ("1-1")]'; - this does not work

In Word I get a Compile error saying the Sub or Function is not Found and it displays the following code:

Private Sub TmpDDE
prt_pgs_rng__ ("1-1")
End Sub

Note the underscores appended to the macro name - not sure where they came from, but when I remove them and continue the Subroutine works.

If you can shed some light I'd appreciate it.

Re: How do I send a VB command to Word that calls a Macro that requires a v

deleted_user — Sat, 10 Jan 2009 03:08:11 GMT

according to http://www2.sas.com/proceedings/sugi29/034-29.pdf, you may send via DDE only Word Commands or VBA subroutines without parameters.

So as a work-around, you may just use the following put statement in SAS,
put '[FilePrint .Pages="1-10"];

Years have passed since that paper, there might be better techniques, but I don't know.

Re: How do I send a VB command to Word that calls a Macro that requires a v

deleted_user — Mon, 12 Jan 2009 21:15:24 GMT

Thanks for your response

You answered one question - it seems you can't call a Word macro that passes parameters

Your workaround makes sense and I could modify the statement within SAS to define the range of pages, but when I tried it as you described it it didn't work.

Do you have any more suggestions?

Re: How do I send a VB command to Word that calls a Macro that requires a v

deleted_user — Mon, 12 Jan 2009 23:41:37 GMT

I don't know why it wouldn't work for you. I'm using Office 2003, and here's an example.
/*
options noxwait noxsync;
data _null_;
call system('start winword');
rc = sleep(2);
run;
filename sas2word dde 'winword|system';

data _null_;
file sas2word;
put '[appactivate("Document1 - Microsoft Word")]';
put 'abcdefg';
put '[sendkeys("^a")]'
'[sendkeys("%o")]'
'[sendkeys("f")]'
'[sendkeys("Comic Sans MS")]'
'[sendkeys("{tab}")]'
'[sendkeys("bold")]'
'[sendkeys("{tab}")]'
'[sendkeys("20")]'
'[sendkeys("%k")]'
'[sendkeys("{enter}")]'
;
put '[fileprint .pages="1-1"]'; *there is a space between fileprint and .pages;
put '[filesaveas("n:\test.doc")]';
put '[filequit()]';
run;
*/
I used only WordBasic functions. Because I don't want to look up very much the reference, I used SendKeys to send key strokes to Word. This function is capable of almost anything, but the key strokes need to be sent in proper order.
For a complete list of WordBasic functions, please go to http://www.microsoft.com/downloads/details.aspx?FamilyID=1A24B2A7-31AE-4B7C-A377-45A8E2C70AB2&displaylang=en.

Re: How do I send a VB command to Word that calls a Macro that requires a v

deleted_user — Tue, 13 Jan 2009 14:27:53 GMT

Hi Urchin,

Thanks for sticking with me on this...

I'm sorry I should have been more specific on how the command didn't work. My file printed, but it printed all the pages - not the range specified.

I tried your code - changing it to read in a file that contained two pages and it still printed out the whole document instead of just the first page.

options noxwait noxsync;
data _null_;
call system('start winword');
rc = sleep(2);
run;
filename sas2word dde 'winword|system';

data _null_;
file sas2word;
put '[fileopen .name = "n:\test.doc"]';
put '[fileprint .pages="1-1"]'; *there is a space between fileprint and .pages;
/*put '[filesaveas("n:\test.doc")]';*/
put '[filequit()]';
run;

So the Print command works, but the pages parameter doesn't.

Re: How do I send a VB command to Word that calls a Macro that requires a v

deleted_user — Tue, 13 Jan 2009 16:06:43 GMT

Sorry I didn't read the WordBasic help document carefully. You need one more argument to print a range of pages. For example,
put '[fileprint .range=4 .pages="2-2"]';

Or, the sendkeys work-around is
put '[sendkeys("^p")]'
'[sendkeys("%g")]'
'[sendkeys("2-2")]'
'[sendkeys("{enter}")]'
;

Hope it helps.

Re: How do I send a VB command to Word that calls a Macro that requires a v

deleted_user — Wed, 14 Jan 2009 16:10:00 GMT

Thank you so much for your help.

The put statement worked with the .range parameter. The only question I have is what does that parameter define and should it always have a value of 4?

Re: How do I send a VB command to Word that calls a Macro that requires a v

deleted_user — Wed, 14 Jan 2009 16:20:22 GMT

Nevermind - I was able to find some documentation that explained it. A .range value of 4 indicates you are going to use .pages to define the range. A .range value of 3 indicates you are going to use .from and .to to define the range.

How cool is that!

My SAS macro now reads in a .lst file, formats it (calling a pre-defined Word Macro), saves it as a .doc file and optionally, prints all or a range of pages.

Thanks again for your help!