Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Export sheets to single PDF file, but only certain sheets

  1. #1
    Board Regular
    Join Date
    Dec 2003
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry Export sheets to single PDF file, but only certain sheets

    As per the title really, i'm looking to export specific sheets from a workbook to a single pdf file, the sheets are 1,3 and 4 plus any sheets found after 7
    I've pretty much run myself into a brick wall with this, i simply can't see how to fix it, so i'm hoping someone here will be able to point out where i've screwed up,

    I get a "subscript out of range" runtime 9 error on the sheet select line,

    Code:
    Sub Export_to_PDF()
    
        'On Error GoTo ErrMessage
        Dim xlVer As Integer
        Dim PdfFilename As Variant
        Dim I As Long
        Dim ArrSheets() As string
    
        ' Disable Screen Updating and Events to speed up the code
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        ' Force Password check before continuing
        If Application.InputBox("Password", "Admin Password Required", "", Type:=2) <> Sheet1.Range("Storage_Cell").Value Then GoTo Leave
        
        
        ' First thing to do is check version in use, because below 2007 the PDF function isn't available
        xlVer = Application.Version
    
        If xlVer < 12 Then    ' v12 = 2007
            MsgBox "You are using a version of Excel which does not support" & vbCrLf & "PDF conversion functions, in order to use this option," & vbCrLf & "please use Excel 2007 or newer.", vbInformation + vbOKOnly, "Option Not Available !"
            GoTo Leave
        End If
        
        ' Now Check if Export to PDF capability is installed (it was an addin for 2007)
        If Not IsPDFLibraryInstalled Then
            ' Show warning message if not installed, as a userform with proper hyperlinks.
            Addin_Required.Show
            GoTo Leave
        End If
        
        
        ' Load SaveAs Dialog, pre-inject file name and path based on this files location
        ' (this potentially can go wrong if invalid filename characters are used in TO Description)
        PdfFilename = Application.GetSaveAsFilename( _
            InitialFileName:=ThisWorkbook.Path & "\" & [Trainee_Rank] & " " & [Trainee_Name] & " (" & [Trg_Obj] & ") Training Record", _
            FileFilter:="PDF, *.pdf", _
            Title:="Export Training Record as PDF")
        
        If PdfFilename <> False Then        ' run export code if filename dialog entry isn't blank or cancelled out of
            ReDim ArrSheets(3)
            ArrSheets(1) = "TO + Trainee Details"
            ArrSheets(2) = "Signatures"
            ArrSheets(3) = "Certificate"
            
            ' Check number of worksheets, sheets after 7 are reports, and need adding to the export selection array
            If ThisWorkbook.Worksheets.Count > 7 Then
                For I = 8 To ThisWorkbook.Worksheets.Count
                    ReDim Preserve ArrSheets(UBound(ArrSheets) + 1)
                    ArrSheets(UBound(ArrSheets)) = Worksheets(I).Name
                Next I
            End If
            
            ' Select all relevant sheets
            ThisWorkbook.Sheets(Array(ArrSheets)).Select
            
            ' Export selected sheets to single PDF format file
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=PdfFilename, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=False, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
            
        End If
        
        ' Skip Error Message
        GoTo Leave
        
        
    ErrMessage:
        MsgBox "  Something has gone wrong during the export process," & vbCrLf & "  It is unlikely that the Training Record was saved.", vbCritical, "Export/Save Error."
        
    Leave:
        ' Re-enable Screen Updating and Event handling
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        
    End Sub

  2. #2
    Board Regular JonXL's Avatar
    Join Date
    Feb 2018
    Posts
    148
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export sheets to single PDF file, but only certain sheets

    Are you trying to select multiple sheets? I'm thinking you can't do that.

    Test the code with only one sheet and see if it still hangs there.

  3. #3
    Board Regular
    Join Date
    Jan 2006
    Posts
    848
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export sheets to single PDF file, but only certain sheets

    Your Select line probably should be
    ThisWorkbook.Sheets(ArrSheets).Select

  4. #4
    Board Regular
    Join Date
    Dec 2003
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export sheets to single PDF file, but only certain sheets

    EDIT: in reply to you can't multi select....

    nope, it's not that, if you record a macro, use ctrl + left cllick to select multiple sheets, then output to pdf, you get this in the recorded output

    Code:
    Sheets(Array("TO + Trainee Details", "Signatures", "Certificate")).Select

    i did however find i had a bug in my code, in that i forgot to check for a visible certificate sheet, if it's hidden, then you get a sheet selection 1004 error
    Last edited by The Gunslinger; Feb 18th, 2018 at 06:31 PM.

  5. #5
    Board Regular
    Join Date
    Dec 2003
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export sheets to single PDF file, but only certain sheets

    Quote Originally Posted by sericom View Post
    Your Select line probably should be
    ThisWorkbook.Sheets(ArrSheets).Select
    yup, tried that as well, same error

  6. #6
    Board Regular
    Join Date
    Jan 2006
    Posts
    848
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export sheets to single PDF file, but only certain sheets

    While it's in error, put the following in the 'Immediate' window and check you are getting the right sheet names
    ?join(arrsheets,",")

  7. #7
    Board Regular
    Join Date
    Dec 2003
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export sheets to single PDF file, but only certain sheets

    Quote Originally Posted by sericom View Post
    While it's in error, put the following in the 'Immediate' window and check you are getting the right sheet names
    ?join(arrsheets,",")
    you've lost me there i'm affraid

  8. #8
    Board Regular
    Join Date
    Jan 2006
    Posts
    848
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export sheets to single PDF file, but only certain sheets

    When your code errors and you have a yellow selected line, click the View menu and select 'Immediate Window', then paste the line in down the bottom and hit enter. It will show you the contents of the array.

  9. #9
    Board Regular
    Join Date
    Dec 2003
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export sheets to single PDF file, but only certain sheets

    Quote Originally Posted by sericom View Post
    When your code errors and you have a yellow selected line, click the View menu and select 'Immediate Window', then paste the line in down the bottom and hit enter. It will show you the contents of the array.
    ok, thank you, and here is what it returned, on the line directly below that text, copied exactly

    ,TO + Trainee Details,Signatures,Certificate,Trg Rprt 1,Wkly Rprt 1,Trg Rprt 2
    now, ignoring the sheets after Certificate, i added them to make sure it's dynamically adding to the array (they are correct btw)..... what i do see is a random "," at the start of the array, and also, if the select instruction is looking for string entries, separated by commas, then the array in it's native foramt won't be correct..... right ??

  10. #10
    Board Regular
    Join Date
    Dec 2003
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export sheets to single PDF file, but only certain sheets

    working from what you put there in regards to the join comand, i thought i'd try flipping it to a text reference, so tried this.....

    Code:
    ThisWorkbook.Sheets(Array(Mid(Join(ArrSheets, """, """), 4) & """")).Select
    playing around with that MID function in outputting to a text variable, and displaying in a msgbox, i was able to manipulate that to return each sheet name, inside the quotes and exactly as the recorded macro displayed it, however, that still didn't work.

    As i said, i'm stuffed, i'm out of ideas and clutching at straws, and every **** example i can find is the same useless code that errors, but every palce i find it say "yeah that works" ... yeah right!
    Last edited by The Gunslinger; Feb 18th, 2018 at 07:38 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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