Form to select sheets and export as PDF

[ron2k_1]

Hi everybody,

I already have the attached It works perfectly SheetsToPrint.xls. It works perfectly when selecting what to print and sending to the default printer. However, I'd like to export to PDF to a specified path in the Drive C.

So this is the code assigned to the "Print" Button:

Private Sub CommandButton2_Click()
Dim iloop As Integer
For iloop = 1 To ListBox1.ListCount
 If ListBox1.Selected(iloop - 1) = True Then
   Sheets(ListBox1.List(iloop - 1, 0)).PrintOut
   ListBox1.Selected(iloop - 1) = False
 End If
 Next
End Sub

I'd like to replace the .PrintOut command with:

.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Temp\" & ActiveSheet.Name & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

But if you try it out yourself you'll note that it only saves the "Start Here" worksheet (which is not even selected on the form).

Kindly advice.

[ron2k_1]

Ok. I've solved my above issue, which was to print all the selected sheets to C:\Temp; I only had Activate the sheets before saving them as PDFs. For those that want to try it out, ensure that you have a folder named "Temp" in your C drive. One more thing; if I'm not mistaken the ExportAsFixedFormat argument can only be used in Excel 2007 onwards. It will be interesting if if works on earlier version. Here is an updated version of the file SheetsToPrint_or_PDF.xls

If you notice I've added two more buttons. One is to print normally to your default printer, another button to save the selected sheets as individual PDF files in C:\Temp. And the objective of the third button is to export the selected sheets as ONE PDF, But I'm failing there. I'm trying the following code (in the attachment) assigned to the last button, but it's not doing it:

Private Sub CommandButton4_Click()
Dim iloop As Integer
For iloop = 1 To ListBox1.ListCount
 If ListBox1.Selected(iloop - 1) = True Then
   Sheets(Array(ListBox1.List(iloop - 1, 0))).Select
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Temp\" & ThisWorkbook.Name & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
   ListBox1.Selected(iloop - 1) = False
 End If
 Next
 Sheets(1).Select
End Sub

Any help will be greatly appreciated.

[blane245]

I am glad you figured this out! Here is a Excel VBA programming tip. There is generally no need to use Select or Activate. You can address an object (workbook, sheet, cell, etc.) as an object without having Excel do the work of selection of activation. In for case, you could do

Private Sub CommandButton4_Click()
Dim iloop As Integer
For iloop = 1 To ListBox1.ListCount
 If ListBox1.Selected(iloop - 1) = True Then
   Sheets(Array(ListBox1.List(iloop - 1, 0))).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Temp\" & ThisWorkbook.Name & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
   ListBox1.Selected(iloop - 1) = False
 End If
 Next
End Sub

This would keep the Excel screen static on Sheet1 while it did the rest of the work.
Happy VBAing!

[ron2k_1]

I haven't tried the portion for CommandButton4 in that manner. But for CommandButton3, which PDFs all the selected sheets as individual sheets to C:\Temp, it will not work like that. You can try it out, and you'll notice that will not work.

EDIT: I've tried your recommendation. And I'm getting object errors.

Anybody with any other tip?

[ron2k_1]

I got it!!!!

Replace the code for CommandButton4, for the following:

Private Sub CommandButton4_Click()
Dim iloop As Integer
For iloop = 1 To ListBox1.ListCount
 If ListBox1.Selected(iloop - 1) = True Then
   Sheets(ListBox1.List(iloop - 1, 0)).Select Replace:=False
   ListBox1.Selected(iloop - 1) = False
 End If
 Next
 
 ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Temp\" & ThisWorkbook.Name & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

 Sheets(1).Select
End Sub

This will allow for continuous printing of all selected worksheets into one file. Wow, this one was tough to find...

EDIT: UPDATED FILE ATTCHED IF IT HELPS ANYBODY

[roki4]

Try to run this in MS Excel 2003 and get the following error:

Run-time Error 438
Object doesn't support this property or method.

This code is highlighted:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Temp\" & Replace(ActiveWorkbook.Name, ".xls", "") & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

[ron2k_1]

Yeah, that kinda confirms what I believed. I think the constant "Type:=xlTypePDF" was introduced in Excel 2007. It runs smoothly on my excel and I have excel 2007. Someone else with xl2007 can run my sheet and confirm to us that it runs smoothly not only on my PC. Did you try it on xl2007 roki4? Did it worked on that version?

Now, I think that you will have to do the inevitable and try to upgrade early. I know many people resit upgrading to excel 2007/2010, but sooner or later it will be necessary.

I suggest two things.
1. Record a macro using your version of XL trying to save the sheet as PDF and replace everything with the exception of the Filename constant part.
2. If your excel cannot support that, then open a new thread and refer to this thread as necessary asking for help in modifying the part that saves the sheet as PDF.

[roki4]

My office only uses MS Excel 2003, so I cannot say. I will have to try it at home with the 2010 version. Thanks for this type of sheet, Ron. It is very useful. I would rep you again but it won't let me! Cheers.

Refer to this link for PDF converting in MS Excel 2003: http://www.ozgrid.com/forum/showthread.php?t=148391

Seems it can only be done via an add-in and not through VBA code.

[ron2k_1]

Thanks for the rep. Once is enough, and glad it may help someone else...

This article confirms what we're finding. I knew I had read it somewhere. http://www.excelguru.ca/content.php?170

Now, it's not the end of the world. Follow this: http://www.excelguru.ca/content.php?161

He used PDFCreator. I have no experience with that free add-on, but it's open source so I can bet it still has support and it's updated regularly.

Study his code, try to understand it. It's not hard, and he gives good notes within his lines of code (look out for the lines that start with a ' ; those are the lines that explains what follows). If you're able to successfully use his code, then it's a matter of calling the code from within my routines. It will be the same as using my short pieces of code - All you'd be doing is replacing the incompatible part of my code with his.

Good luck