Activating multiple worksheets for export using VBA

Question

I am trying to code a routine which takes a user's choices of available worksheets from an Excel VBA form and exports them into one PDF document. I plan on using this with export to Word and to PowerPoint routines. I have tried several of the ideas from Stack Overflow which were said to have worked. I have had no luck. I have also tried various ideas from other sources...maybe I am blind to the obvious.

I tried using an array (arrSheets, still in the code, I have hopes that maybe I can still use it.). It populates with sheet objects, but using a dynamic array and the redim command haven't worked for me.

What I have here is code which appears to work well until it comes to the "ActiveSheet.ExportAsFixedFormat..." line. At that point I get "Application-defined or Object-defined error (Run-time error 1004)"

The following code fires from a command button on a VBA form...

Private Sub cmdExport_Click()
    'Find the selected documents from the form's checkboxes and send to the export routine
Dim intArrayCounter, intSelectionNum As Integer
Dim bolFound As Boolean
Dim ctrl As control
Dim arrSheets(1 To 6) As Variant ' the array to hold the worksheet objects...

    intSelectionNum = 0 ' which checkbox is it
    intArrayCounter = 1 ' array index
    bolFound = False    ' was a checked box found?

    For Each ctrl In frmToPDF.Controls
        If TypeName(ctrl) = "CheckBox" Then
            intSelectionNum = intSelectionNum + 1 ' set the selection number
            If ctrl.Value = True Then
                bolFound = True '  found a selection set the flag to true

                    Set arrSheets(intArrayCounter) = Sheets(intSelectionNum)
                    ThisWorkbook.Sheets(intSelectionNum).Select

                ' increment the counter
                intArrayCounter = intArrayCounter + 1
            End If
        End If
    Next

    'Sheets(arrSheets).Select <---remmed out cause this throws an error

    If bolFound = False Then   ' if there is Nothing selected send a message, or do the deal...
        Call MsgBox("There is nothing selected to export!", vbOKOnly, "Nothing selected...")
    Else
        frmExport.Caption = "Processing the document...Please be patient!"

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\test.pdf", Quality:=xlQualityStandard, IgnorePrintAreas:=False, DisplayFileAfterPublish:=True
        'the above Activesheet routine throws "Application-defined or Object-defined error  (Run-time error 1004)"
    End If

    ThisWorkbook.Sheets(intSheet).Select

End Sub

Answer 1

The only reason of failure that I can think of for your ExportAsFixedFormat statement is that the option DisplayFileAfterPublish is not enabled in your installation (typical if you don't have Acrobat Reader installed). You can check it when you try to export manually to PDF, the option "open file after publishing" must be disabled.

Try removing the parameter DisplayFileAfterPublish:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\test.pdf", _
    Quality:=xlQualityStandard, IgnorePrintAreas:=False

As for the other questions in your post, I dont see anything wrong in putting Worksheet objects in an array. But Sheets(arrSheets).Select is ill-formed and morover unnecessary. It is ill-formed because the arrSheets argument is an array containing sheet references, while it should be an array of indices, that's all.

Also I can't tell why Redim hasn't worked for you, because you did not show how you used it.

Answer 2

I could not get the "ActiveSheet.ExportAsFixedFormat" to work. But I found this answer elsewhere on StackOverflow which works very well, and with very little code: Use a method on multiple sheets simultaneously without using ActiveSheet or Select Thank you all for your help...

Answer 3

This works for me. It has the defect (which also existed in OP's original) that the sheets selected may not be the ones you intended since you're selecting on sheet number according to the tabindex of the checkboxes on the form. How you correct for this is up to you, it's out of scope here.

Private Sub CommandButton1_Click()
    Dim intSelectionNum As Integer
    Dim bolFound As Boolean
    Dim ctrl As Control

    bolFound = True     ' was a checked box found? True = NO
    intSelectionNum = 0 ' which checkbox is it

    For Each ctrl In FrmToPDF.Controls
        If TypeName(ctrl) = "CheckBox" Then
            intSelectionNum = intSelectionNum + 1 ' set the selection number
            If ctrl.Value = True Then
                ThisWorkbook.Sheets(intSelectionNum).Select bolFound
                bolFound = False ' from now on we extend the selection
            End If
        End If
    Next

    If bolFound = True Then   ' if there is Nothing selected send a message, or do the deal...
        MsgBox "There is nothing selected to export!", vbOKOnly, "Nothing selected..."
    Else
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\test.pdf", Quality:=xlQualityStandard, IgnorePrintAreas:=False
    End If
End Sub

What's happening here: I am overloading bolFound to set an optional Worksheets.Select parameter - True means replace the current slection, False means extend the current selection. By starting bolFound as True, I nullilfy any existing selection. bolFound is then set to False, which extends the selection for subsequent sheets. I don't need an array as I am making Excel manage the selection for me.

Note: ThisWorkbook.Sheets(intSelectionNum).Select only selects one sheet at a time (default is True), so OP's code as posted would only export one sheet at a time no matter how many boxes were checked.

I have Acrobat Readeer installed and , DisplayFileAfterPublish:=True worked for me, but I have omitted that part from the example.

Bonus discussion: Like OP's code the example selects "Sheets" instead of "Worksheets". This allows it to export e.g. Charts, Excel4 macros and dialogs, and so on (though by now only the Charts will of interest to most people). If I changed "Sheets" to "Worksheets" it would export only worksheets.

Mandatory disclaimer: this is example code to export selected sheets to a PDF. It has not been extensively tested and is not intended to be a drop-in solution. It works for me. Depending on your system you may need to tweak it.

Edited to add: to find out what sheets were selected (e.g. to build your array to use for other purposes), you can insert this after the For/Next loop finishes:

dim sht as Object
For Each sht In Application.ActiveWindow.SelectedSheets
    Debug.Print sht.Name
Next