none
Excel to PDF export - Each row as one pdf file RRS feed

  • Question

  • Hi All

    I am fairly new to complex excel programming but recently have to work for excel export to pdf. Happened to see this forum and felt fortunate enough. I am working on version excel 2007. I used this post as reference to start with basics of pdf export: https://social.msdn.microsoft.com/Forums/Lync/en-US/27ac7ece-e1af-4155-bf4b-c5cba7a08eb7/macro-to-create-excel-data-to-pdf-file?forum=exceldev

    However, while trying to create the very first code in the post, I am thrown with error:  Run-time error '5': Invalid procedure call or argument. In debug mode,  highlight goes on the line ActiveSheet.ExportAsFixedFormat .....The same code works fine in 2013. Can someone guide me where could be the problem. 

    I am trying to achieve below functionality, from experts here I would like to know if this is possible from Excel directly or need some other programming. Thanks in advance

    I have an excel sheet with data till columns DL starting from A. I need to export every row of data from excel to as one PDF. Ex: 30 rows - 30 pdfs. Also Data needs to be written in a particular format into my pdf (nearly 30 columns data into pdf).

    Say, Col A, Col B, Col C has data Employee ID, Employee Name, Employee Join Date,

    Col A   |   Col B   | Col C

    0001    |  John     | 08/23/2017

    0002     |  Jack     |  07/21/2016

    my pdf must be:
                          Employee Details - John
    1. Name of the Employee :   John
    2.  ID of the Employee  :   0001
    3. Date of Joining      : 08/23/2017

    Regards

    Mathangi

    Wednesday, August 23, 2017 12:05 PM

Answers

  • Pre-2010 PDF export requires an add-in be loaded:

    https://www.microsoft.com/en-us/download/details.aspx?id=7

    To get your specific format, set up another sheet with your labels, and formulas with INDEX functions where your data should go, like so:

    =INDEX(Sheet1!A:A,$A$1)

    =INDEX(Sheet1!B:B,$A$1)

    =INDEX(Sheet1!C:C,$A$1)

    Then enter a number in A1 (using code, from 2 - the first row with data, usually - to the count of employees), re-calc, then save that sheet as a PDF.

    Wednesday, August 23, 2017 8:06 PM
  • Hi Mathangi,

    Could you save as PDF manually now? I agree with Bernie that you need load the Microsoft Save as PDF or XPS add-in, load it and check if you could save as PDF manually.

    For your format issue, I suggest you create such a worksheet.


    After that, you could use a loop to export data from data sheet into this sheet and then export this sheet as PDF.

    Here is the example.

    Sub ExportPDF()
    
    Dim pdfSheet As Worksheet
    
    Dim dataSheet As Worksheet
    
    Set pdfSheet = ActiveWorkbook.Sheets("PDFSheet")
    
    Set dataSheet = ActiveWorkbook.Sheets("DataSheet")
    
    Application.ScreenUpdating = False
    
    For i = 2 To 3
    
    ID = dataSheet.Cells(i, 1)
    
    Name = dataSheet.Cells(i, 2)
    
    JoinDate = dataSheet.Cells(i, 3)
    
    pdfSheet.Cells(1, 1).Value = "Employee Details - " & Name
    
    pdfSheet.Cells(2, 2).Value = Name
    
    pdfSheet.Cells(3, 2).Value = ID
    
    pdfSheet.Cells(4, 2).Value = JoinDate
    
    pdfSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    
    "C:\Users\Desktop\ExcelFile\" & Name
    
    Next i
    
    Application.ScreenUpdating = True
    
    End Sub

    Best Regards,

    Terry

    Thursday, August 24, 2017 5:13 AM

All replies

  • Pre-2010 PDF export requires an add-in be loaded:

    https://www.microsoft.com/en-us/download/details.aspx?id=7

    To get your specific format, set up another sheet with your labels, and formulas with INDEX functions where your data should go, like so:

    =INDEX(Sheet1!A:A,$A$1)

    =INDEX(Sheet1!B:B,$A$1)

    =INDEX(Sheet1!C:C,$A$1)

    Then enter a number in A1 (using code, from 2 - the first row with data, usually - to the count of employees), re-calc, then save that sheet as a PDF.

    Wednesday, August 23, 2017 8:06 PM
  • Hi Mathangi,

    Could you save as PDF manually now? I agree with Bernie that you need load the Microsoft Save as PDF or XPS add-in, load it and check if you could save as PDF manually.

    For your format issue, I suggest you create such a worksheet.


    After that, you could use a loop to export data from data sheet into this sheet and then export this sheet as PDF.

    Here is the example.

    Sub ExportPDF()
    
    Dim pdfSheet As Worksheet
    
    Dim dataSheet As Worksheet
    
    Set pdfSheet = ActiveWorkbook.Sheets("PDFSheet")
    
    Set dataSheet = ActiveWorkbook.Sheets("DataSheet")
    
    Application.ScreenUpdating = False
    
    For i = 2 To 3
    
    ID = dataSheet.Cells(i, 1)
    
    Name = dataSheet.Cells(i, 2)
    
    JoinDate = dataSheet.Cells(i, 3)
    
    pdfSheet.Cells(1, 1).Value = "Employee Details - " & Name
    
    pdfSheet.Cells(2, 2).Value = Name
    
    pdfSheet.Cells(3, 2).Value = ID
    
    pdfSheet.Cells(4, 2).Value = JoinDate
    
    pdfSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    
    "C:\Users\Desktop\ExcelFile\" & Name
    
    Next i
    
    Application.ScreenUpdating = True
    
    End Sub

    Best Regards,

    Terry

    Thursday, August 24, 2017 5:13 AM
  • Thank you very much Bernie and Terry. Will try these and give an update. 
    Thursday, August 24, 2017 6:49 AM
  • Hi Mathangi,

    What's your current issue now? Has your issue been solved? Please feel free to let us know your testing result and your current issue.

    Best Regards,

    Terry

    Sunday, August 27, 2017 11:43 PM
  • Hi Terry

    Yes solutions provided by you and Bernie, worked great. I am currently working on adding inputs to my form and based on which the PDF must be created. 

    Regards

    Mathangi

    Thursday, August 31, 2017 12:18 PM
  • Hi! I found a PDF Builder that allows you convert each row into a PDF in just seconds, check this Add-on of Google Sheets. You can just import the Excel into Google Sheets and then generate the PDF. Check it here https://gsuite.google.com/marketplace/app/excel_rows_to_pdf/36271926254 and let me know if that works for you ;)
    Wednesday, August 19, 2020 12:51 AM
  • This solution worked perfect for me, allthough i am struggling with another thing.

    If my data looked like this :

    Say, Col A, Col B, Col C has data Employee ID, Employee Name, Employee Join Date,

    Col A   |   Col B   | Col C
    0001    |  John     | 08/23/2017

    0001    |  Mark     | 08/23/2018
    0002     |  Jack     |  07/21/2016

    0002     |  Patrick     |  07/21/2017
    How could i make my PDF look like this with the suggested code ?

    Summarized : each PDF could/should contain a variabel number of rows depending on the value in column A

    my pdf must be:


                          PDF1
    0001   John    08/23/2017
    0001   Mark    08/23/2018

                           PDF2

    0002     Jack     07/21/2016

    0002     Patrick  07/21/2017

    Is there a way to do this using the above code or similar ?

    Friday, October 9, 2020 11:02 AM