Return to Homepage

Goto the Tip of the Month Archive

Other interesting pages ...
LinkedIn Profile
SAS Cheat Sheet
Useful SAS Code
Full SAS Example
Basic Statistics
Contact Information

SAS Tip of the Month
April 2015
(for VBA)

This month I am going away from SAS briefly and into the world of VBA, but this tip is still very useful for SAS programmers.

Typically when someone wants to print all MS Word or RTF files from a directory the common way is to select all the relevant files and select print which will print all pages of all documents selected. There are cases however where this is a waste of paper and we only need to print a few pages of each document. This can be easily carried out using the VBA code below:

  01 Sub PrintFirstPages()
  02   Dim strFileName As String
  03   Dim strPath As String
  04   strPath = "c:\temp\"
  05   strFileName = Dir(strPath + "*.rtf", vbNormal) 
  06   Do While strFileName <> ""
  07     Documents.Open FileName:=strPath + strFileName
  08     Documents(strFileName).PrintOut Range:=wdPrintFromTo, From:="1", To:="2"
  09     Documents.Close
  10     strFileName = Dir
  11   Loop
  12 End Sub

Line 1 defines the procedure, and lines 2 and 3 set the two internal variables. Line 4 specifies the directory path to use - - note, it must end with the '\'. Line 5 selects the files to print -- in this example it is the files with the extension .RTF but this is modifiable. Line 6 starts the loop where each file is opened (lines 7 and 8) and the range of pages specified in line 10 is printed -- in this code pages 1 and 2 are printed, however if there is only one page in the document then that page is printed. The document is finally closed at lines 11 and 12, and the next file in the selection is selected for print at line 12.

To enter the VBA code into Microsoft Word select

  Tools > Macro > Visual Basic Editor

then

  Insert > Module

then paste the code above and then press

  File > Close and Return to Microsoft Word

The sequence above may vary due to the version of Microsoft Word that is on your system, but the basic steps are the same.

The VBA macro is now ready for use within Word and can be accessed from the VBA macro library.

Hope this was useful.

________________________________
Updated April 2, 2015