none
Export individual worksheets in excel to pdf file RRS feed

  • Question

  • I have an excel spreadsheet that has about 40 worksheets that I need to export to individual pdf files. I am using VS 2012 and vb language. I also need to have the headers print on each page if possible. Can anyone help with a solution for this (and sample code).

    Thanks.

    Monday, June 8, 2015 7:06 PM

Answers

  • Thanks for all the replies. I actually figured out the header part by creating a macro in excel using 'repeat rows at top' and then inserting it into my code (adjusting for syntax). Here is the complete code including printing the headers on each page (assuming the header rows were always rows 1-6):

    Dim xlsApp = New Microsoft.Office.Interop.Excel.Application
    Dim xlsBook As Microsoft.Office.Interop.Excel.Workbook
    Dim paramExportFormat As XlFixedFormatType = XlFixedFormatType.xlTypePDF
    Dim paramExportQuality As XlFixedFormatQuality = XlFixedFormatQuality.xlQualityStandard
    Dim paramOpenAfterPublish As Boolean = False
    Dim paramIncludeDocProps As Boolean = True
    Dim paramIgnorePrintAreas As Boolean = True
    Dim paramFromPage As Object = Type.Missing
    Dim paramToPage As Object = Type.Missing
           
    xlsApp.ScreenUpdating = False
    xlsBook = xlsApp.Workbooks.Open(sourcePDF & "MFA Budget P&L.xlsx", UpdateLinks:=False, ReadOnly:=False)           
               
    For Each ws In xlsBook
         xlsBook.Sheets(ws.name).Select()
         xlsBook.Sheets(ws.name).PageSetup.PrintTitleRows = "$1:$6"
         xlsBook.ActiveSheet.ExportAsFixedFormat(paramExportFormat, "c:\test\testfile.pdf", paramExportQuality, paramIncludeDocProps, paramIgnorePrintAreas, paramFromPage, paramToPage, paramOpenAfterPublish)
    Next

    xlsBook.Close(SaveChanges:=False)
    xlsApp.Quit()

    • Marked as answer by J-Bal Thursday, June 11, 2015 7:05 PM
    Thursday, June 11, 2015 7:05 PM

All replies

  • Ok, I am getting close. Using the code below I was able to export the entire spreadsheet as a pdf file. Now I just need to be able to do individual sheets (and keep the headings on each page).


    Imports Microsoft.Office.Interop.Excel

    Dim xlsApp = New Microsoft.Office.Interop.Excel.Application
    xlsApp.ScreenUpdating = False
    Dim xlsBook As Microsoft.Office.Interop.Excel.Workbook
    Dim paramExportFormat As XlFixedFormatType = XlFixedFormatType.xlTypePDF
    Dim paramExportQuality As XlFixedFormatQuality = XlFixedFormatQuality.xlQualityStandard
    Dim paramOpenAfterPublish As Boolean = False
    Dim paramIncludeDocProps As Boolean = True
    Dim paramIgnorePrintAreas As Boolean = True
    Dim paramFromPage As Object = Type.Missing
    Dim paramToPage As Object = Type.Missing
    xlsBook = xlsApp.Workbooks.Open(sourcePDF & "test.xlsx", UpdateLinks:=False, ReadOnly:=False)
    xlsBook.ExportAsFixedFormat(paramExportFormat, sourcePDF & "test.pdf", paramExportQuality, paramIncludeDocProps, paramIgnorePrintAreas, paramFromPage, paramToPage, paramOpenAfterPublish)
    xlsBook.Close(SaveChanges:=False)
    xlsApp.Quit()

      
    Monday, June 8, 2015 7:55 PM
  • Hi,

    Since it is the development issue, I help you move this case to the Excel for Developers forum for dedicated support.

    Best Regards,

    Jack


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 9, 2015 9:18 AM
  • Hi J-Bal,

    Base on your code, you are using the Workbook.ExportAsFixedFormat method, so to export each worksheet, you could use Worksheet.ExportAsFixedFormat method.

    Regards                      

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 9, 2015 9:50 AM
    Moderator
  • I was able to successfully split the sheets into individual pdf files using the code below. Now I just need to be able to code it so it will print the headers on each page. Does anyone know how to do this?

    thanks.

    Dim xlsApp = New Microsoft.Office.Interop.Excel.Application
    xlsApp.ScreenUpdating = False
    Dim xlsBook As Microsoft.Office.Interop.Excel.Workbook
    Dim xlsworksheet As Microsoft.Office.Interop.Excel.Worksheet
    Dim paramExportFormat As XlFixedFormatType = XlFixedFormatType.xlTypePDF
    Dim paramExportQuality As XlFixedFormatQuality = XlFixedFormatQuality.xlQualityStandard
    Dim paramOpenAfterPublish As Boolean = False
    Dim paramIncludeDocProps As Boolean = True
    Dim paramIgnorePrintAreas As Boolean = True
    Dim paramFromPage As Object = Type.Missing
    Dim paramToPage As Object = Type.Missing
    xlsBook = xlsApp.Workbooks.Open(sourcePDF & "test.xlsx", UpdateLinks:=False, ReadOnly:=False)
    xlsBook.Sheets("Sheet1").Select()
    xlsBook.ActiveSheet.ExportAsFixedFormat(paramExportFormat, sourcePDF & "test1.pdf", paramExportQuality, paramIncludeDocProps, paramIgnorePrintAreas, paramFromPage, paramToPage, paramOpenAfterPublish)
    xlsBook.Sheets("Sheet2").Select()
    xlsBook.ActiveSheet.ExportAsFixedFormat(paramExportFormat, sourcePDF & "test2.pdf", paramExportQuality, paramIncludeDocProps, paramIgnorePrintAreas, paramFromPage, paramToPage, paramOpenAfterPublish
    xlsBook.Close(SaveChanges:=False)
    xlsApp.Quit()

    Tuesday, June 9, 2015 12:16 PM
  • If you want to select the sheets that you will convert to PDF, this will do it for you.

    Sub luxation()
        ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
        Selection.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:="C:\Users\rshuell001\Desktop\Coding\temp.pdf", _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
    End Sub

    If you want to save all sheets as separate files, use this.

    http://www.rondebruin.nl/win/s3/win007.htm

    Then, use this Macro to convert all Excel files in a folder into PDF files.

    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
    


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, June 9, 2015 4:23 PM
  • Hi J-Bal,

    Which header that you want to print? What’s the data like of the sheet? What do you want the PDF like?

    On the other hand, with word 2013, we could open PDF file through word, so we could edit the content by using the word object model.

    Regards                      

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 10, 2015 2:31 AM
    Moderator
  • This method based on .NET Excel Component, works greatly at my side for exporting each worksheet as an individual PDF document

    Dim workbook As New Workbook()
    workbook.LoadFromFile("sample.xlsx")
    For i As Integer = 0 To workbook.Worksheets.Count - 1
    	Dim sheet As Worksheet = workbook.Worksheets(i)
    	sheet.SaveToPdf(String.Format("result-{0}.pdf", i), FileFormat.PDF)
    Next

    Thursday, June 11, 2015 7:23 AM
  • Thanks for all the replies. I actually figured out the header part by creating a macro in excel using 'repeat rows at top' and then inserting it into my code (adjusting for syntax). Here is the complete code including printing the headers on each page (assuming the header rows were always rows 1-6):

    Dim xlsApp = New Microsoft.Office.Interop.Excel.Application
    Dim xlsBook As Microsoft.Office.Interop.Excel.Workbook
    Dim paramExportFormat As XlFixedFormatType = XlFixedFormatType.xlTypePDF
    Dim paramExportQuality As XlFixedFormatQuality = XlFixedFormatQuality.xlQualityStandard
    Dim paramOpenAfterPublish As Boolean = False
    Dim paramIncludeDocProps As Boolean = True
    Dim paramIgnorePrintAreas As Boolean = True
    Dim paramFromPage As Object = Type.Missing
    Dim paramToPage As Object = Type.Missing
           
    xlsApp.ScreenUpdating = False
    xlsBook = xlsApp.Workbooks.Open(sourcePDF & "MFA Budget P&L.xlsx", UpdateLinks:=False, ReadOnly:=False)           
               
    For Each ws In xlsBook
         xlsBook.Sheets(ws.name).Select()
         xlsBook.Sheets(ws.name).PageSetup.PrintTitleRows = "$1:$6"
         xlsBook.ActiveSheet.ExportAsFixedFormat(paramExportFormat, "c:\test\testfile.pdf", paramExportQuality, paramIncludeDocProps, paramIgnorePrintAreas, paramFromPage, paramToPage, paramOpenAfterPublish)
    Next

    xlsBook.Close(SaveChanges:=False)
    xlsApp.Quit()

    • Marked as answer by J-Bal Thursday, June 11, 2015 7:05 PM
    Thursday, June 11, 2015 7:05 PM