locked
Convert Excel Data into PDF file RRS feed

  • Question

  •  I've been trying to create a macro that would allow me to automatically import excel data into a pdf template. I need help figuring out how to make the code create a unique pdf for each line in the excel file while importing all the correct data in the right fields.

    I am new to creating macros and would appreciate all the help I can get. Also, if there's a better application to use instead of excel I am willing to learn. If my question isn't really clear please let me know. 

    Thanks in advance! 

    Thursday, July 26, 2018 5:42 PM

Answers

  • The script below will loop through all Excel files in a folder, and convert each to a PDF file.

    Sub Convert_Excel_To_PDF()
        Dim MyPath As String, FilesInPath As String
        Dim MyFiles() As String, Fnum As Long
        Dim mybook As Workbook
        Dim CalcMode As Long
        Dim sh As Worksheet
        Dim ErrorYes As Boolean
        Dim LPosition As Integer
     
        'Fill in the path\folder where the Excel files are
        MyPath = "c:\Documents and Settings\shuerya\Desktop\ExcelFiles\"
     
        FilesInPath = Dir(MyPath & "*.xl*")
        If FilesInPath = "" Then
            MsgBox "No files found"
            Exit Sub
        End If
     
        Fnum = 0
        Do While FilesInPath <> ""
            Fnum = Fnum + 1
            ReDim Preserve MyFiles(1 To Fnum)
            MyFiles(Fnum) = FilesInPath
            FilesInPath = Dir()
        Loop
     
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .EnableEvents = False
        End With
     
        If Fnum > 0 Then
            For Fnum = LBound(MyFiles) To UBound(MyFiles)
                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
                On Error GoTo 0
     
                If Not mybook Is Nothing Then
     
                        
                        LPosition = InStr(1, mybook.Name, ".") - 1
                        mybookname = Left(mybook.Name, LPosition)
                        mybook.Activate
                        'All PDF Files get saved in the directory below:
                        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                            "C:\Documents and Settings\shuerya\Desktop\PDFFiles\" & mybookname & ".pdf", _
                            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                            :=False, OpenAfterPublish:=False
                            
                End If
                
                mybook.Close SaveChanges:=False
     
            Next Fnum
        End If
     
        If ErrorYes = True Then
            MsgBox "There are problems in one or more files, possible problem:" _
                 & vbNewLine & "protected workbook/sheet or a sheet/range that not exist"
        End If
     
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = CalcMode
        End With
    End Sub
    



    MY BOOK

    • Marked as answer by Farida12 Wednesday, October 10, 2018 1:38 PM
    Tuesday, August 7, 2018 3:52 PM

All replies

  •  I've been trying to create a macro that would allow me to automatically import excel data into a pdf template. I need help figuring out how to make the code create a unique pdf for each line in the excel file while importing all the correct data in the right fields.

    I am new to creating macros and would appreciate all the help I can get. Also, if there's a better application to use instead of excel I am willing to learn. If my question isn't really clear please let me know. 

    Thanks in advance! 

    Thursday, July 26, 2018 8:31 PM
  • Hello Farida12,

    >>I need help figuring out how to make the code create a unique pdf for each line in the excel file while importing all the correct data in the right fields.

    Do you want to print each line data to a pdf? What do you mean importing correct data in the right fields?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 27, 2018 1:38 AM
  • So I have a template i created on excel for creating a packing slip(invoices) and i need to fill in each field. But it gets very time consuming when I have about 100+ packing slips to create and I have to fill them in manually before turning into a pdf. I need help figuring out if there's a way I can just auto fill the template with the necessary information and if there's a code I can create that will take the all the data in the excel file(eah line is unique) and create individual pdfs for them. I hope I am explaining myself better. 
    Friday, July 27, 2018 1:15 PM
  • The script below will loop through all Excel files in a folder, and convert each to a PDF file.

    Sub Convert_Excel_To_PDF()
        Dim MyPath As String, FilesInPath As String
        Dim MyFiles() As String, Fnum As Long
        Dim mybook As Workbook
        Dim CalcMode As Long
        Dim sh As Worksheet
        Dim ErrorYes As Boolean
        Dim LPosition As Integer
     
        'Fill in the path\folder where the Excel files are
        MyPath = "c:\Documents and Settings\shuerya\Desktop\ExcelFiles\"
     
        FilesInPath = Dir(MyPath & "*.xl*")
        If FilesInPath = "" Then
            MsgBox "No files found"
            Exit Sub
        End If
     
        Fnum = 0
        Do While FilesInPath <> ""
            Fnum = Fnum + 1
            ReDim Preserve MyFiles(1 To Fnum)
            MyFiles(Fnum) = FilesInPath
            FilesInPath = Dir()
        Loop
     
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .EnableEvents = False
        End With
     
        If Fnum > 0 Then
            For Fnum = LBound(MyFiles) To UBound(MyFiles)
                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
                On Error GoTo 0
     
                If Not mybook Is Nothing Then
     
                        
                        LPosition = InStr(1, mybook.Name, ".") - 1
                        mybookname = Left(mybook.Name, LPosition)
                        mybook.Activate
                        'All PDF Files get saved in the directory below:
                        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                            "C:\Documents and Settings\shuerya\Desktop\PDFFiles\" & mybookname & ".pdf", _
                            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                            :=False, OpenAfterPublish:=False
                            
                End If
                
                mybook.Close SaveChanges:=False
     
            Next Fnum
        End If
     
        If ErrorYes = True Then
            MsgBox "There are problems in one or more files, possible problem:" _
                 & vbNewLine & "protected workbook/sheet or a sheet/range that not exist"
        End If
     
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = CalcMode
        End With
    End Sub
    



    MY BOOK

    • Marked as answer by Farida12 Wednesday, October 10, 2018 1:38 PM
    Tuesday, August 7, 2018 3:52 PM
  • Thank you so much for your help! This helped a lot!
    Wednesday, October 10, 2018 1:39 PM