Once upon a time, we were required to convert all RTF files into PDF files. And there were hundreds of files which should be converted. It was obviously a headache to complete this task fully manually. What’s worse, we had to re-run each time source data was changed. Everyone in our team complained. At that time, I developed a VBA macro which can convert all RTF files within a folder into PDF files.
Introduction on VBA macro to convert RTF files into PDF files
Here is how the macro looks like. Initially, you only need to fill cell B2 and cell C2.
Once you click on the first button “Get File Name”, Excel will clear contents of all cells from 2nd row except for cell B2 and cell C2. Then it will retrieve filename of all rtf files within folder in Cell B2 (“D:\Users\xxx_xxxxx\Desktop” in this post) and put the filenames in column A one by one. While at the same time, Excel will also copy inpath folder from cell B2 to other cells in Column B (B3 to B7 in our case) as long as cells in column A are not missing. It will also copy cell C2 through other cells in column C (C3 to C7 in our example).
By clicking on 2nd button “rtf2pdf”, Excel will check if corresponding PDF file exists. PDF file will be deleted if exists. For example, in our case, t140102.pdf will be deleted from folder “D:\Users\xxx_xxxxx\Desktop”. Next it will check if t140102.rtf exists in “D:\Users\xxx_xxxxx\Desktop”. Only when this file exists, Excel will convert it to PDF files and place the newly created PDF file into folder “D:\Users\xxx_xxxxx\Desktop”. VBA macro will enable Excel to loop through all non-blank cells in column A except for cell A1 until all files are converted.
Finally, it will save this Excel file which means that folders that you place in cell B2 and C2 will be saved. All information, from column A to column C, where are filled by Excel will also be saved. Next time, there is no need for you to click on “Get File Name” even if source data change and you are required to re-run.
VBA macro to retrieve filename of all rtf files within a folder
Here presents how to create FileSystemObject. VBA FileSystemObject can offer you an object based approach to access your computer’s file system and with this approach you can read, create or delete files and even folders.
Click here to hide/show code
Sub Getfname_Click()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
lastrow = ThisWorkbook.Worksheets(“rtf2pdf macro”).Cells(Rows.Count, “A”).End(xlUp).Row
‘Clear content
For j = 2 To lastrow
Cells(j, 1) = “”
Cells(j + 1, 2) = “”
Cells(j + 1, 3) = “”
Next j
‘Create an instance of the FileSystemObject
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
‘Get the folder object
inpath = Trim(ThisWorkbook.Worksheets(“rtf2pdf macro”).Cells(2, 2))
Set objFolder = objFSO.GetFolder(inpath)
i = 2
‘loops through each file in the directory and prints their names and path
For Each objFile In objFolder.Files
temp = Split(objFile.Name, “.”)
If temp(UBound(temp)) = “rtf” Then
‘print file name
Cells(i, 1) = temp(0)
i = i + 1
End If
Next objFile
lastrow = ThisWorkbook.Worksheets(“rtf2pdf macro”).Cells(Rows.Count, “A”).End(xlUp).Row
For j = 3 To lastrow
Cells(j, 2) = Cells(2, 2)
Cells(j, 3) = Cells(2, 3)
Next j
MsgBox “Done!”
End Sub
VBA macro to convert RTF files into PDF files
Here you will learn how to create a Microsoft Word object and use it to open a word document and then save it as pdf.
Click here to hide/show code
Sub RTF2PDF_Click()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim fnm As String
Dim Testfnm As String
Dim objWord As Object
Set objWord = CreateObject(“Word.Application”)
objWord.Visible = True
lastrow = ThisWorkbook.Worksheets(“rtf2pdf macro”).Cells(Rows.Count, “A”).End(xlUp).Row
For i = 2 To lastrow
fnm = Trim(ThisWorkbook.Worksheets(“rtf2pdf macro”).Cells(i, 1))
inpath = Trim(ThisWorkbook.Worksheets(“rtf2pdf macro”).Cells(i, 2))
outpath = Trim(ThisWorkbook.Worksheets(“rtf2pdf macro”).Cells(i, 3))
Testfnm1 = Dir(inpath & “\” & fnm & “.rtf”)
Testfnm2 = Dir(outpath & “\” & fnm & “.pdf”)
‘Delete pdf file
If Testfnm2 > “” Then
wbname = outpath & “\” & fnm & “.pdf”
Kill wbname
End If
‘rtf2pdf
If Testfnm1 > “” Then
Dim objWordDoc As Word.Document
Set objWordDoc = objWord.Documents.Open(inpath & “\” & fnm & “.rtf”)
With objWordDoc
.ExportAsFixedFormat OutputFileName:=outpath & “\” & fnm & “.pdf”, ExportFormat:=wdExportFormatPDF
.Close SaveChanges:=False
End With
End If
Next i
ThisWorkbook.Save
objWord.Quit
MsgBox “Done!”
End Sub
Note:
For this macro, Adobe Acrobat Pro is not required and even Adobe Reader is not required. Since it mimics the way how to save a rtf file into pdf file using Microsoft Word. You only need to have Microsoft Excel and Microsoft Word installed in your computer. And here I have to remind you that do not forget to choose References. Click on Tools -> References to open Reference dialog box and then tick all necessary references showed in following picture.
If you don’t know how to insert a button, please read this post.
