Consulting

Results 1 to 2 of 2

Thread: VBA Mail Merge for making labels

  1. #1

    VBA Mail Merge for making labels

    Hi, all this is my first time posting. I regularly use the mail merge function to make labels, but the problem that I'm having is each label corresponds to one excel file, and sometimes I have to go through many files at a time. I'm trying to see if there is a way to have a macro look into a folder and make a pdf of each excel file in said folder. Just need it to open the file make the pdf of it and then move on to the next file in the folder. If anyone has some insight into this it would be much appreciated! Thank you!

  2. #2
    I am not sure of the relevance of the labels, however https://www.gmayor.com/document_batch_processes.htm will convert all the documents in a folder (its sub folders too if you require it) to PDF.

    If you want to roll your own then the above is based on the following process, albeit it includes additional error handling which the following lacks
    Option Explicit
    
    Sub BatchSaveAsPDF()
    'Graham Mayor - https://www.gmayor.com - Last updated - 03 Dec 2021
    Dim strFile As String
    Dim strPath As String
    Dim strPDFName As String
    Dim intPos As Integer
    Dim oDoc As Document
    Dim fDialog As FileDialog
        Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
        With fDialog
            .Title = "Select folder and click OK"
            .AllowMultiSelect = False
            .InitialView = msoFileDialogViewList
            If .Show <> -1 Then
                MsgBox "Cancelled By User", , "List Folder Contents"
                Exit Sub
            End If
            strPath = fDialog.SelectedItems.Item(1)
            Do Until Right(strPath, 1) = "\"
                strPath = strPath & "\"
            Loop
        End With
        strFile = Dir$(strPath & "*.docx")
        While strFile <> ""
            Set oDoc = Documents.Open(strPath & strFile)
            intPos = InStrRev(strFile, ".")
            strPDFName = Left(strFile, intPos)
            strPDFName = strPath & strPDFName & "pdf"
            oDoc.ExportAsFixedFormat _
                    OutputFileName:=strPDFName, _
                    ExportFormat:=wdExportFormatPDF, _
                    OpenAfterExport:=False, _
                    OptimizeFor:=wdExportOptimizeForPrint, _
                    Range:=wdExportAllDocument, From:=1, To:=1, _
                    Item:=wdExportDocumentContent, _
                    IncludeDocProps:=True, _
                    KeepIRM:=True, _
                    CreateBookmarks:=wdExportCreateHeadingBookmarks, _
                    DocStructureTags:=True, _
                    BitmapMissingFonts:=True
            oDoc.Close SaveChanges:=wdDoNotSaveChanges
            strFile = Dir$()
            DoEvents
        Wend
    lbl_Exit:
        Set oDoc = Nothing
        Set fDialog = Nothing
        Exit Sub
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •