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
May 2011

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

I recently had the situation where a number of RTF files had to be converted into Text files. Rather than open each file and save it as a text, it was necessary to find a way to do this via a program for every file in a specified directory. It could have been done in SAS, but a VBA macro in Word was a better solution. The following is the code that made up the VBA macro:

   Sub Rtf2Txt()

       Dim strFileName As String
       Dim strOutFileName As String
       Dim strPath As String

       'Select the directory where RTF files are located
       With Application.FileDialog(msoFileDialogFolderPicker)
       .InitialFileName = "C:\"
       .Show
       strPath = .SelectedItems(1)
       End With

       'All *.RTF files in the directory selected
       strFileName = Dir(strPath + "\*.rtf", vbNormal)
       Do While strFileName <> ""
           'Create an output file name from original name
           strOutFileName = Replace(strFileName, ".rtf", ".txt")
           strOutFileName = Replace(strOutFileName, ".RTF", ".txt")
           'Open RTF File and save as a TXT file
           Documents.Open FileName:=strPath + "\" + strFileName, _
               ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
               PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
               WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
               wdOpenFormatAuto, XMLTransform:=""
           ActiveDocument.SaveAs FileName:=strPath + "\" + strOutFileName, _
               FileFormat:=wdFormatText, LockComments:=False, Password:="", _
               AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
               EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
               :=False, SaveAsAOCELetter:=False, Encoding:=1252, InsertLineBreaks:=True _
               , AllowSubstitutions:=False, LineEnding:=wdCRLF
           Documents.Close
           ' Get the next file in the directory
           strFileName = Dir
       Loop

   End Sub

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.

Note that this VBA code was tested on Word 2003 and 2007, does not have error capture code, and is given "as is" without any implied warranty or support.

I hope this is useful. If you are going to PharmaSUG this year, I will be there -- say 'HI!'. See you in June.

________________________________
Updated May 3, 2011