of the Month
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
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