Inserting text into a bookmarked table cell in word using DDE from SAS

[eguva]

Hi,

I am trying to insert a text into the table of a word document using SAS and DDE.say, one of the cell in the table has text "version:". I have a bookmark right after the : and truing to insert some text here. But, it is not working. It is working if I am trying to insert text at bookmarks outside the table.
Does any one know the easiest method to insert text into cells of a table.Here is the code that I am using.


options noxwait noxsync;
x 'c:\msoffice\winword\winword.exe';

filename ws dde 'winword|system';
filename name dde 'winword|C:\Documents and Settings\......\t.doc!name';
filename location dde 'winword|C:\Documents and Settings\......\t.doc!address';
filename prod dde 'winword|C:\Documents and Settings\......\t.doc!tab';

data _null_;
x=sleep(10);
file ws;
put '[FileOpen .Name = "C:\Documents and Settings\.....\t.doc"]';
stop;
run;

data _null_;
/*file name notab;
put 'Alice';
file location notab;
put 'Wonderland';*/
file prod notab;
put "ente";
stop;
run;

data _null_;
file ws;
put '[FilePrint]';
put '[FileSave]';
put '[FileExit 2]';
stop;
run;

Thanks,
Eguva

 

[klaz2002]

This is how I do it. I first create the bookmark in a template.doc file. I then "edit" that bookmark and save the file as a another word doc.
Here is an example:

  data _null_;
        file myword; *** this is the DDE triplet ***;
        Inv_Name  = 'Test_Invoice '||trim(left(put(date(),yymmdd10.)))||'.doc';

        dateText  = put(date(),worddate20.);
        WordMonth = put(month(date())-1,monthf.);

        put '[AppMinimize]';
        put '[ChDefaultDir "' "&InvoicePath" '",0]';
        put '[FileOpen("template.doc")]';
        put '[EditBookmark("IDate",0,0,0,1)]';
        put '[Insert "' datetext '"]';
        put '[FileSaveAs.Name="' inv_name '"]';
        put '[FileClose]';
    run;

In short I use the 'edit' command with the 'Insert' command. (IDate is the name of the bookmark.)

Hope that this helps you.
Klaz

 

[eguva]

Hi Klaz,

Than you for your response.
I came to know that instead of bookmarking individual cells, it is easy to bookmark a table and build the table accordingly.

Thanks,
Kiran

 

[eguva]

Hi Klaz,

Than you for your response.
I came to know that instead of bookmarking individual cells, it is easy to bookmark a table and build the table accordingly.

Thanks,
Eguva

 

[eguva]

Hi Klaz,

Is there any code that I can use to calculate the number of bookmarks in a word document to maket he program more dynamic.

Thanks,
Eguva.

 

[klaz2002]

I remember in WordBasic that there is a function called 'CountBookmarks()' that will return the total bookmarks in the active doc.
Klaz

 

[eguva]

Hi,

Is there a special character that I can insert into the variable values, such that the values wrap to next line(not next cell or row) in a word table at desired places in a particular column?

Say, I have a long stringed values in a variable which goes into a particular column and I want the long strig to get wrapped at particular points. Ist here a special character that word would recognize?

Thanks,
Eguva

 

[klaz2002]

Eguva,
The answer is yes you can insert the character needed to do what you ask. But, I question the need for this as you can set the cell in the table to handle the wrapping of text.

What you need to pass to Word in your string is the carrage return and line feed values. These are '0D' and '0A' hex values. Here is my sample program:

    options noxwait noxsync;
    x "C:\Program Files\Microsoft Office\OFFICE11\winword.exe";

    data _null_;
      x = sleep(1);
    run;
    filename myword dde "Winword|system"; 

    data _null_;
        file myword;
        teststring= "this is a test "||'0D'x||'0A'x||"of my system";
        put datetext=;
        

        PUT '[AppMinimize]';
        PUT '[ChDefaultDir "c:\temp\",0]';
        PUT '[FileOpen("doc2.doc")]';


        put '[EditBookmark("test",0,0,0,1)]';
        put '[Insert "' teststring '"]';

       

        put '[FileSaveAs.Name="doc4.doc"]';

        put '[FileClose]';

    run;

I set a word doc named doc2.doc as my template. There I created a table with a bookmark in a cell named 'test'. My results were a word doc named doc4.doc that had the string I created wrapped exactly where the CR & LF values were placed.

I hope that this helps you.
Klaz

 

[eguva]

Hi Klaz,

Thank you for the sample program.
I am writing to a word template with bookmarks.In this template I have to create a new table from scratch too, as i do not know how many rows I would be inserting into the table.SO i bookmarked the whole table instead of individual cell and inserting text from an already created dataset. So in the values for the mentioned 2 columns of the created table, I want the text to wrap wherever there is a space.
Eg: if the string in a cell of the mentioned columns is "1=Numberone 2=Nmbertwo 3=Numberthree" and so on.
Now how do I code to wrap eaxctly at the spaces.SO I want something like:
1=Numberone
2=Nmbertwo
3=Numberthree

Thanks,
Eguva

 

[klaz2002]

Eguva,
You could use the tranwrd() function and replace the spaces with the '0a0d'x code I gave you earlier.
ex

 Your_string = tranwrd(source,' ','0a0d'x);

On another note, why do you need to know how many rows there are in your table? Why not use the 'NextCell' command and have Word move to where you need to go. When Word reaches the end of the current table it will automatically add the next row if needed. (Meaning, if you have more data to insert, then Word will add the next row and move the pointer to the 'NextCell'.

Klaz

 

[eguva]

Hi Klaz,

I have created a string that is exactly as your text string and tried to use in put statement. But it didn to work.

Probably I am not explaining you right.Let me explain my situation clearly.I have a word table and I have a bookmark for the whole table. Now how do I tell to word to wrap exactly at points where there are spaces for each cell of a particular column. This is a very dynamic program, so table columns remain same, but the cell contents differ.So, I cannot bookmark the cell again.

Thanks,
Kiran

 

[klaz2002]

You must be more specific. What didn't work? I just tried a sample of the code I wrote and then pasted the output string into Word and it does work. So please explain how you did it and what doesn't work.
Klaz

 

[eguva]

Hi Klaz,

Here is the code that I am using.
mark10 is the bookmark for a blank space in my word template where a table is written from scratch.

I need to get the text wrapped at places where there is space for columns 4(var4).I tried to replace/concatenate '0D0A'x at those spaces in the variable values. eg: var4="This is a test line1"||'0D0A'x||"This is a test line2"||'0D0A'x||..........and so on.......;
When I am outputting with the code below, then the text after each space in the variable var4 is wrapping, but moving into first column and that too each bit in a separate row. IF i use just '0D'x, then I am getting a square shaped symbol at the places where space exists in the word doc.

So, now I have a table in word and in one particular column, i need to replace space with something that can help the text wrap appropriately.Your code is working fine and i am doing the same thing here, but does not work.

data final;
length row $5000.;
set final;
row=trim(left(var1))||&tab.||trim(left(var2))||&tab.||trim
(left(var3))||&tab.||trim(left(var4))||&tab.||trim(left
(var5))||&tab.||trim(left(var6));
run;

data _null_;
file insert10;
set final end=last;
if _n_=1 then do;
put 'Row1' &tab 'Row2' &tab 'Row3' &tab 'Row4'
&tab 'Row5' &tab 'Row6';
end;
put row;
run;

data _null_;
file sas2word ;
put '[EditGoto.Destination="mark10"]'; put '[TextToTable.ConvertFrom="1",.NumColumns="6",.InitialColWidth="Auto",.Format="16",.Apply="63"]';
run;

data _null_;
file sas2word;
put '[EditSelectAll]';
put '[Font "Arial"]';
put '[FontSize 10]';
put '[FileSaveAs.Name="'"#####"'",.Format=0]';
put '[FileSave]';
put '[docclose 1]';
run;


Thanks,
Kiran

 

[klaz2002]

Eguva,
Could you have a table in the word doc before you create the table on the fly using the texttotable function? What I mean is if you had a one row table (sort of like a template) you could insert the string and move to the next cell in the row. By using the insert and the nextcell functions you would be inserting your data and creating the numbe rof rows you need. I have created your string using the '0D0A'x the way you have documented this and it has worked.

ex.

    data _null_;
      set final end=last;
        file myword;
        length LabelText $3000;
        
        %*** ONLY EXECUTE THESE COMMANDS ONCE ***;
        if _n_ eq 1 then do;
         put '[AppMinimize]';
         put '[FileOpen("' "TemplateFullFileName.doc" '")]';

         %*** SELECT DEFAULT TABLE - IN A TEMPLATE ONLY ONE TABLE ***;
         put '[TableSelectTable]' / '[StartOfRow]';
        end;
 
        put '[Insert "' row '"]';
        
    
        put '[NextCell]';
        

        if last then do;
          put '[FileSaveAs.Name=' 'TargetFullFileNamenew.doc' '"]';
          put '[FileExit(2)]';
        end;

I hope that this helps you.
Klaz

 

[eguva]

Hi Klaz,

I got it.I used FINDREPLACE command to replace a dummy characterthat i have created with ^p for new paragraph.
Also, I trid that next cell command to write a table, but it is very slow.It writes cell by cell and it is taking a long time.I am not sure ifthat just happened to me.

Thanks,
Kiran

 

[eguva]

Hi,

I have a table written to word using SAS and DDE.
Now I want to know the word basic command to repeat the header row in each page if the table spans to more than one page.

Thanks,
Eguva

 

[klaz2002]

I think its TableHeadings but you may have to look that up. Its a WordBasic command I just don't remember the correct syntax.
Klaz

 

[eguva]

Hi Klaz,

Thank you. That helped.
For the program that i have discussed above, i need to have a word document/file open already for the program to work.
Is it possible to programatically specify to open the word docuemtn too instead.

Thanks,
Eguva

 

[klaz2002]

Sure,
Like this.

  PUT '[AppMinimize]';
  PUT '[ChDefaultDir "' "&Path" '",0]';
  PUT '[FileOpen("YourFile.doc")]';
  ***
   your code
  ***;
  PUT '[FileSaveAs.Name="' "&newfilename"  '"]';
  PUT '[FileClose]';

Hope this helps you.
Klaz

 

[eguva]

Hi Klaz,

Can ou please help me get rid of this error "ERROR: Physical file does not exist, winword|system"

Here is my program:

%let template_path=%str(C:\test\template.doc);
%let maxmarks=15 /*Total number of bookmarks in the template*/

x 'c:\msoffice\winword\winword.exe';
filename sas2word dde 'winword|system' lrecl=5000;

data _null_;
x=sleep(3);
file sas2word;
PUT '[AppMinimize]';
put '[FileOpen.Name = "'"&template_path."'"]';
run;

%do i=1 %to &maxmarks.;
filename insert&i. dde "winword|&template_path.!&bkmkprfx.&i." notab lrecl=5000;
%end;

data _null_;
%do i=1 %to &maxmarks.;
file insert&i.;
put "test string";
%end;

All i am looking is to have the program automatically open word application.i do not want to have an application opened.

Thanks,
Eguva