none
Creating macro converting docx files into pdf RRS feed

  • Question

  • Hello,

    I'm trying to create a macro that automatically find all .docx and .xlsx files into selected folder and convert them into PDFs files. I already make something but it converts too much (for example gifs files). Can you help me in that?

    Thanks in advance for your help.
    Robert
    Friday, February 20, 2015 1:51 PM

Answers

  • Run this macro from Word:

    Sub ConvertWord2PDF()
        Dim strFolder As String
        Dim strFile As String
        Dim doc As Document
        Dim lngPos As Long
        Dim strPDFName As String
        With Application.FileDialog(4)
            If .Show Then
                strFolder = .SelectedItems(1)
            Else
                MsgBox "You didn't specify a folder!", vbExclamation
                Exit Sub
            End If
        End With
        If Right(strFolder, 1) <> "\" Then
            strFolder = strFolder & "\"
        End If
        Application.ScreenUpdating = False
        strFile = Dir(strFolder & "*.doc*")
        Do While strFile <> ""
            Set doc = Documents.Open(FileName:=strFolder & strFile, _
                AddToRecentFiles:=False)
            lngPos = InStrRev(strFile, ".")
            strPDFName = Left(strFile, lngPos) & "pdf"
            doc.ExportAsFixedFormat OutputFileName:=strFolder & strPDFName, _
                ExportFormat:=wdExportFormatPDF
            doc.Close SaveChanges:=True
            strFile = Dir
        Loop
        Application.ScreenUpdating = True
    End Sub

    and this one from Excel:

    Sub ConvertExcel2PDF()
        Dim strFolder As String
        Dim strFile As String
        Dim wbk As Workbook
        Dim lngPos As Long
        Dim strPDFName As String
        With Application.FileDialog(4)
            If .Show Then
                strFolder = .SelectedItems(1)
            Else
                MsgBox "You didn't specify a folder!", vbExclamation
                Exit Sub
            End If
        End With
        If Right(strFolder, 1) <> "\" Then
            strFolder = strFolder & "\"
        End If
        Application.ScreenUpdating = False
        strFile = Dir(strFolder & "*.xls*")
        Do While strFile <> ""
            Set wbk = Workbooks.Open(Filename:=strFolder & strFile)
            lngPos = InStrRev(strFile, ".")
            strPDFName = Left(strFile, lngPos) & "pdf"
            wbk.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFolder & strPDFName
            wbk.Close SaveChanges:=True
            strFile = Dir
        Loop
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by RobRaj Monday, February 23, 2015 12:35 PM
    Friday, February 20, 2015 2:35 PM

All replies

  • Hello Robert,

    Office applications provide (for example, Word) the ExportAsFixedFormat method which saves a document as PDF or XPS format. So, you can use automation for opening files and then exporting them using the PDF file format.

    You may find the following links helpful:

    Creating VBA Add-ins to Extend and Automate Microsoft Office Documents

    How to automate Microsoft Word to create a new document by using Visual C#

    Also you may consider using Open XML SDK. See Welcome to the Open XML SDK 2.5 for Office in MSDN.

    Friday, February 20, 2015 2:33 PM
  • Run this macro from Word:

    Sub ConvertWord2PDF()
        Dim strFolder As String
        Dim strFile As String
        Dim doc As Document
        Dim lngPos As Long
        Dim strPDFName As String
        With Application.FileDialog(4)
            If .Show Then
                strFolder = .SelectedItems(1)
            Else
                MsgBox "You didn't specify a folder!", vbExclamation
                Exit Sub
            End If
        End With
        If Right(strFolder, 1) <> "\" Then
            strFolder = strFolder & "\"
        End If
        Application.ScreenUpdating = False
        strFile = Dir(strFolder & "*.doc*")
        Do While strFile <> ""
            Set doc = Documents.Open(FileName:=strFolder & strFile, _
                AddToRecentFiles:=False)
            lngPos = InStrRev(strFile, ".")
            strPDFName = Left(strFile, lngPos) & "pdf"
            doc.ExportAsFixedFormat OutputFileName:=strFolder & strPDFName, _
                ExportFormat:=wdExportFormatPDF
            doc.Close SaveChanges:=True
            strFile = Dir
        Loop
        Application.ScreenUpdating = True
    End Sub

    and this one from Excel:

    Sub ConvertExcel2PDF()
        Dim strFolder As String
        Dim strFile As String
        Dim wbk As Workbook
        Dim lngPos As Long
        Dim strPDFName As String
        With Application.FileDialog(4)
            If .Show Then
                strFolder = .SelectedItems(1)
            Else
                MsgBox "You didn't specify a folder!", vbExclamation
                Exit Sub
            End If
        End With
        If Right(strFolder, 1) <> "\" Then
            strFolder = strFolder & "\"
        End If
        Application.ScreenUpdating = False
        strFile = Dir(strFolder & "*.xls*")
        Do While strFile <> ""
            Set wbk = Workbooks.Open(Filename:=strFolder & strFile)
            lngPos = InStrRev(strFile, ".")
            strPDFName = Left(strFile, lngPos) & "pdf"
            wbk.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFolder & strPDFName
            wbk.Close SaveChanges:=True
            strFile = Dir
        Loop
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by RobRaj Monday, February 23, 2015 12:35 PM
    Friday, February 20, 2015 2:35 PM