Print Page | Close Window

Print from UserForm

Printed From: www.exp-systems.com
Category: PDF reDirect
Forum Name: Using PDF reDirect
Forum Discription: Questions and Comments on using PDF reDirect Freeware and Pro
URL: http://www.exp-systems.com/Forum_exp/forum_posts.asp?TID=224
Printed Date: 06 Dec 22 at 7:51AM


Topic: Print from UserForm
Posted By: go4saint
Subject: Print from UserForm
Date Posted: 30 May 06 at 2:22AM
Hi,
I'm novice in VBA programming and I need some help.

I have to print multiple excel sheets in one pdf file.
I included clsPrint_PDF_Pro class module and the Sample PDF Code in my Worksheet and it works fine.

But I want to append this function to my Menu, and select the printer,path,file name and which sheets to print from an UserForm and I get this error : "PrintOut method of Worksheet clas failed" and in debug points to this line of code :

ThisWorkbook.Sheets(1).PrintOut 1, 1, 1, 0, sPrinter




Replies:
Posted By: Michel_K17
Date Posted: 30 May 06 at 8:32AM
Hi,

   Can you send me your file? I can take a quick look if you want. Send it as a zip file to my e-mail at exp_[at]_exp-systems.com.

Michel


Posted By: Michel_K17
Date Posted: 30 May 06 at 8:34PM
Hi,

   I received your sample code. Thank you. I see the problem. Your file is an Excel addin (.xla), and the code is running within the user form. Therefore, the object "ThisWorkbook" will not refer to the other Excel files that your users have loaded in Excel.

   What you need to do is to use the "Application" object which will allow you to enumerate through all the Workbooks and all the sheets that are open in Excel, and to add them to your listbox.

   Here is some sample code for your userform:

Private Sub UserForm_Initialize()
    Dim MyWorkbook As Workbook
    Dim MySheet As Worksheet
    For Each MyWorkbook In Application.Workbooks

        ' Make sure that this is not myself

        If MyWorkbook.name <> "GKVmenu v6.xla" Then

            ' Add this workbook to the list

            For Each MySheet In MyWorkbook.Sheets
               ListBox1.AddItem MyWorkbook.name & " >> " & MySheet.name
            Next MySheet
        End If
    Next MyWorkbook
End Sub


   Finally, for the Printout command, you need to use something like this:

Application.Workbooks("WorkbookName").Sheets("SheetName").Printout1, 1, 1, 0, sPrinter


   You can figure out the value of "WorkbookName" and "SheetName" from what the user selected in your Listbox2 control.

   Cheers!

Michel


Posted By: go4saint
Date Posted: 01 Jun 06 at 10:27AM
I still have trouble with this code in UserForm:

I get "Subscript out of range"
I highlighted in red where i didn't know what to introduce.

What i nedded was to print all sheets listed in ListBox2 in one PDF, and if it is possible to print selected sheets from all open workbooks in one PDF is very good.


'Example code showing how to create PDF files using PDF reDirect Pro Batch Printers

' API Declarations
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long



Private Sub UserForm_Initialize()
    Dim MyWorkbook As Workbook
    Dim MySheet As Worksheet
    For Each MyWorkbook In Application.Workbooks
        ' Make sure that this is not myself
        If MyWorkbook.name <> "GKVmenu v6.xla" Then
            ' Add this workbook to the list
            For Each MySheet In MyWorkbook.Sheets
               ListBox1.AddItem MyWorkbook.name & " >> " & MySheet.name
            Next MySheet
        End If
    Next MyWorkbook
End Sub


Private Sub remove_Click()

If ListBox2.ListIndex >= 0 Then
ListBox1.AddItem ListBox2.Text
ListBox2.RemoveItem ListBox2.ListIndex
End If

End Sub


Private Sub removeall_Click()

Do While ListBox2.ListCount > 0
ListBox1.AddItem ListBox2.List(0)
ListBox2.RemoveItem (0)
Loop

End Sub

Private Sub addall_Click()

Do While ListBox1.ListCount > 0
ListBox2.AddItem ListBox1.List(0)
ListBox1.RemoveItem (0)
Loop

End Sub

Private Sub add_Click()

If ListBox1.ListIndex >= 0 Then
ListBox2.AddItem ListBox1.Text
ListBox1.RemoveItem ListBox1.ListIndex
End If

End Sub


Private Sub printpdf_Click()

    Dim oPDF As New clsPrint_PDF_Pro
    Dim Result As Long
    Dim sPath As String
    Dim sFilename As String
    
    ' Recover Path & Filename from Sheet
    sPrinter = boxPrinter.Text
    sPath = boxPath.Text '.Range("D26").Formula
    sFilename = boxFilename.Text '.Range("D28").Formula
    
    ' Setup the PDF output path and filename
    Result = oPDF.SetOutput(sPrinter, sPath, sFilename)
    
    Dim arrayprint() As String
    Dim N As Integer
    N = 0
    For i = 0 To ListBox2.ListCount - 1
        If ListBox2.Selected(i) = True Then
            N = N + 1
            ReDim Preserve arrayprint(1 To N)
            arrayprint(N) = ListBox2.List(i)
        End If
    Next i
    If N = 0 Then
        MsgBox "You must select at least one Sheet"
        Exit Sub
    End If
       
   
    ' Check that there were no problems
    If Result = 1 Then
        ' No error occured: print to the Batch PDF Printer
       
        Application.Workbooks("test").Sheets(arrayprint).Printout1 , 1, 1, 0, sPrinter

       
        ' BONUS CODE THAT CHECKS FOR OUTPUT PDF
        '--------------------------------------
        ' Optional: this code is to wait until the file is created.
        '           Do this if you want to open the PDF in the PDF Reader.
        '           WARNING 1: the Excel Application will appear to "freeze"
        '           unless you add a progress bar of some kind (see the
        '           MS Word Example).
        '           WARNING 2: In the Batch Printer Preferences, make sure
        '           you do not select the option that will add a sequence
        '           number to the filename, otherwise, the target filename
        '           will never be found.
       
' Uncomment the next few lines to add optional code
'        ' Keep checking for the output file, up to 1 minute maximum
'        Dim MaxTime2Unlock As Single
'        MaxTime2Unlock = Timer() + 60
'
'        ' Start the loop
'        Do While MaxTime2Unlock >= Timer And Not CheckFileCreated(sPath & "\" & sFilename & ".pdf")
'            ' Share the CPU with other programs
'            Sleep (100)
'            DoEvents
'        Loop
'
'        ' Now, show the PDF in the PDF Reader
'        If CheckFileCreated(sPath & "\" & sFilename & ".pdf") Then
'            Call ShellExecute(0, "Open", sPath & "\" & sFilename & ".pdf", "", "", 3)
'        Else
'            ' Could not find the target PDF
'            MsgBox "ERROR: Output PDF not found."
'        End If

    Else
        ' An error occured, which can be as follows:
        ' 0 = UNKNOWN_ERROR
       
        ' PRINTER ERRORS
        ' 100 = PRINTER_NOT_FOUND
        ' 101 = COULD_NOT_ENUMERATE_PRINTERS
        ' 102 = COULD_NOT_OPEN_PRINTER
        ' 103 = COULD_NOT_SET_PRINTER_SETTINGS
        ' 104 = PRINTER_NOT_BATCH_PDF_PRINTER
       
        ' PATH ERRORS
        ' 105 = PATH_IS_NOT_VALID
        ' 106 = PATH_IS_EMPTY
        ' 107 = PATH_IS_GREATER_THAN_255_CHARACTERS
       
        ' FILENAME ERRORS
        ' 108 = FILENAME_IS_EMPTY
        ' 109 = FILENAME_IS_GREATER_THAN_200_CHARACTERS

        MsgBox "An Error occured while setting the Batch PDF." & vbCrLf & _
               "Error Number: " & Str(Result), _
               vbCritical, _
               "Critical Error"
    End If
    
    ' All done


End Sub


Private Function CheckFileCreated(FileName As String) As Boolean
    On Error GoTo ErrorHandler
    CheckFileCreated = False
    Name FileName As FileName
    CheckFileCreated = True
ErrorHandler:
End Function



Posted By: lovetski
Date Posted: 01 Jun 06 at 10:38AM

Hi!

Workbooks is a collection of all workbooks open in MS Excel. "Test" is the name of a workbook that Michel tested the code with.

You should substitute the name of a workbook there.

Look into Excel's VBA help. You'll sure find some examples of using these objects in your code.

HTH



-------------
Andrew.


Posted By: go4saint
Date Posted: 01 Jun 06 at 11:17AM
"Test" is a acctually my file I'm working with.


Posted By: Michel_K17
Date Posted: 01 Jun 06 at 7:18PM
Hi,

   Make sure that you places quotes (") around the name of the workbooks and of the sheets. Therefore, your line:

   Application.Workbooks("test").Sheets(arrayprint).Printout1 , 1, 1, 0, sPrinter

Should actually be:

   Application.Workbooks("test").Sheets("arrayprint").Printout1 , 1, 1, 0, sPrinter

   Cheers!

Michel.


Posted By: go4saint
Date Posted: 10 Jun 06 at 3:04AM
Hi,

Sorry for delay, I was away for few days.

Still not working, any others ideas ?

Thanks!


Posted By: go4saint
Date Posted: 13 Jun 06 at 5:17AM
I allmost solve this problem, but I have one more request.

When I print one sheet witch has more than one page it is printing only first page. The same happens when I select multiple sheets to print.

Even if it is writing "Printing page 1 of 7" the result PDF file contains only the first page.


Another strange things happens! Some times it prints only the first page, then when I print multiple sheets it print first 2 or 3 sheets in one file, next 2 or 3 sheets in another PDF file, and so on.   


Thanks.


Posted By: Michel_K17
Date Posted: 13 Jun 06 at 9:15PM
Hi,

   Glad to hear that it is working better. Multiple PDF files will be created if the Sheets of the Excel File have different "Page Setup" settings. Although Microsoft does not call that a bug, they have posted a problem description and a fix on their web site.

   Anyway, you can find out all of the details on my forum [ http://www.exp-systems.com/forum_exp/forum_posts.asp?TID=31&KW=Excel - here ].

   Cheers!

Michel


Posted By: go4saint
Date Posted: 14 Jun 06 at 6:44AM
Yes, you are right!

It finally works!

Thanks!


Posted By: Michel_K17
Date Posted: 15 Jun 06 at 1:29AM

 

   Cool!




Print Page | Close Window