![]() |
![]() ![]() ![]() ![]() |
![]() ![]() |
Using PDF reDirect | |
![]() |
Topic: Print from UserForm | ![]() ![]() |
Author | Message |
go4saint
Newbie ![]() ![]() Joined: 30 May 06 Posts: 6 |
![]() ![]() ![]() 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 Edited by go4saint |
|
![]() |
|
Michel_K17
Moderator Group ![]() ![]() Forum Administrator Joined: 25 Jan 03 Posts: 1673 |
![]() ![]() ![]() |
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 |
|
![]() |
|
Michel_K17
Moderator Group ![]() ![]() Forum Administrator Joined: 25 Jan 03 Posts: 1673 |
![]() ![]() ![]() |
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 |
|
![]() |
|
go4saint
Newbie ![]() ![]() Joined: 30 May 06 Posts: 6 |
![]() ![]() ![]() |
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 |
|
![]() |
|
lovetski
Newbie ![]() Joined: 18 Apr 06 Location: Russian Federation Posts: 30 |
![]() ![]() ![]() |
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.
|
|
![]() |
|
go4saint
Newbie ![]() ![]() Joined: 30 May 06 Posts: 6 |
![]() ![]() ![]() |
"Test" is a acctually my file I'm working with.
|
|
![]() |
|
Michel_K17
Moderator Group ![]() ![]() Forum Administrator Joined: 25 Jan 03 Posts: 1673 |
![]() ![]() ![]() |
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. |
|
![]() |
|
go4saint
Newbie ![]() ![]() Joined: 30 May 06 Posts: 6 |
![]() ![]() ![]() |
Hi,
Sorry for delay, I was away for few days. Still not working, any others ideas ? Thanks! |
|
![]() |
|
go4saint
Newbie ![]() ![]() Joined: 30 May 06 Posts: 6 |
![]() ![]() ![]() |
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. Edited by go4saint |
|
![]() |
|
Michel_K17
Moderator Group ![]() ![]() Forum Administrator Joined: 25 Jan 03 Posts: 1673 |
![]() ![]() ![]() |
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 [here]. Cheers! Michel |
|
![]() |
|
go4saint
Newbie ![]() ![]() Joined: 30 May 06 Posts: 6 |
![]() ![]() ![]() |
Yes, you are right!
It finally works! Thanks! |
|
![]() |
|
Michel_K17
Moderator Group ![]() ![]() Forum Administrator Joined: 25 Jan 03 Posts: 1673 |
![]() ![]() ![]() |
Cool! |
|
![]() |
|
![]() ![]() |
||
Forum Jump |
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |