Excel vba code to print to pdf

[Stevolah]

Specifically I've built a user form that will allow the user to select a host of reports (or excel tabs with defined print areas) to be printed either in hard copy via the default printer or to print to PDF and save in the respective monthly folder.

There are 2 check boxs one for hard copyand one to print to PDF (a PDF Creator printer in a hosted/Citrix cloud environment). I can't seem to correctly select the PDF printer?

I'm a self taught VBA accontant, not a programmer so please try keep it simple the code I'm using is as follows

Sub PrintToPDF(strSheetToPrint)


Dim strFolder As String
Dim strPathToSaveTo As String
Dim strCurrentPrinter As String
Dim svInputPS


strFolder = Range("TB_FOLDER").Offset(0, (periodx)).Value 'lookup for current month folder name
strPathToSaveTo = "S:\Finance\FY2014\CONSOLIDATED\" & strFolder & "" 'specific path to select in dialogue box
strCurrentPrinter = Application.ActivePrinter ' save the currently active printer
svInputPS = strFolder & "-" & strSheetToPrint & ".pdf" ' defines name of file to be saved

For i = 0 To 15
curNePrint = Format(i, "00")
On Error Resume Next
Application.ActivePrinter = "\\tc1-ps-ETC1\ETC_PDF Creator on wave-pdf1:" & curNePrint & ":"
Next i

Worksheets(strSheetToPrint).PrintOut PrToFileName:=svInputPS, PrintToFile:=True
ActiveSheet.PrintOut ' print the sheet1
Worksheets(strSheetToPrint).PrintOut

Application.ActivePrinter = strCurrentPrinter ' change back to the original printer

End Sub

Most of my code I find on forums like this and copy and tinker with to get it to do what I need. I keep getting an error on selecting the PDF printer...??

Any ideas?

Thanks

 

[diddi]

I have had trouble getting this .activeprinter method to work reliably, so I ended up shelling to printUI and changing the default printer to pdf creator and then shell again to revert.

 

[Stevolah]

I've managed to get the code to call the dialogue box but I can't get the macro to populate the File location and file names?

Its using Foxit...how do I know the fields to call?

 

[diddi]

I use bullzip, which is free, and I set my file name in bullzip as a default name, then rename the file to the appropriate location. that way the user does not have to worry about dialog boxes and thinking too hard. not sure if you can do the same in foxit (or can you shell the parameters to foxit?)