?
Solved

Save Excel Worksheet with Primo PDF using Code

Posted on 2010-11-12
8
Medium Priority
?
1,529 Views
Last Modified: 2012-05-10
Hello,

Is it possible to use code to print an excel worksheet using Primo PDF? I want to convert an excel worksheet to PDF and save it with a specific name using code (user selects a button or auto prints when opend).

Thanks - Mike
0
Comment
Question by:mi_tuttle
  • 4
  • 4
8 Comments
 
LVL 12

Expert Comment

by:snailcat
ID: 34125059
Not sure if you can do this with Primo PDF.  You can use VB to select Primo PDF as the printer and then print but you cannot give it a specific name.

If you move up to Excel 2007 or 2010 the ability to save as a pdf exists and you can use this code:

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

Author Comment

by:mi_tuttle
ID: 34125158
No plans to upgrade to 2007 for a while. Do you have any other suggestions?

What I am trying to accomplish is to have a daily scheduled task open an excel workbook, run some code to update the sheets, and then have one of the sheets saved to a folder as a pdf file.

I've created the scheduled task and developed the code to do the updates to the workbook. What I'm having problems with is developing the code to save the final sheet to a pdf.
0
 
LVL 12

Expert Comment

by:snailcat
ID: 34125253
I don't know much about Primo PDF but does it show up as a printer?

Do you want the file name of the saved pdf to be the same each time?

You can just use a printout VB command and select the active printer as the Primo PDF printer--

Application.ActivePrinter = "activeprintername:"
 ActiveSheet.PrintOut

This will probably prompt the user for a file name for the pdf though
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mi_tuttle
ID: 34125335
Primo PDF printer = "PrimoPDF on Ne04:". I did create a little code below but as you mentioned it prompts the user which I'm wanting to avoid. Also I would like to save the file each day with a unique identifier (filename_date).


Sub PrintPage()

Dim strCurrentPrinter As String

    strCurrentPrinter = Application.ActivePrinter ' save the currently active printer
    On Error Resume Next ' ignore  errors
    Application.ActivePrinter = "PrimoPDF on Ne04:" ' change to PrimoPdf
    Sheets("Report").PrintOut ' print the sheet1
    Application.ActivePrinter = strCurrentPrinter ' change back to the original printer
    On Error GoTo 0 ' resume normal error handling
   
End Sub I
0
 
LVL 12

Expert Comment

by:snailcat
ID: 34125395
Unfortunately I don't see a way to send characters to the popup screen that will ask the user for the filename.

It will not recognize a sendkeys while the save as pdf dialog is open

Sorry I couldn't be more helpful
0
 

Accepted Solution

by:
mi_tuttle earned 0 total points
ID: 34137596
No luck with PrimoPDF but I was able to make it work with PDFCreator.

Sub PrintToPDF()        'Print to PDF file using PDFCreator

    Dim pdfjob As PDFCreator.clsPDFCreator, sPDFName As String, sPDFPath As String

    'Set Output file name and save location
    sPDFName = "test_" & Format$(Date, "mm-dd-yyyy") & ".pdf"
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator

    'Check if worksheet is empty and exit if so
    If IsEmpty(Sheets("Report").UsedRange) Then Exit Sub

    Set pdfjob = New PDFCreator.clsPDFCreator

    With pdfjob
        If .cStart("/NoProcessingAtStartup") = False Then
            MsgBox "Can't initialize PDFCreator.", vbCritical + _
                    vbOKOnly, "PrtPDFCreator"
            Exit Sub
        End If
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        .cClearCache
    End With

    'Print the document to PDF
    Sheets("report").PrintOut Copies:=1, ActivePrinter:="PDFCreator"

    'Wait until the print job has entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = 1
        DoEvents
    Loop
    pdfjob.cPrinterStop = False

    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
        DoEvents
    Loop
    pdfjob.cClose
    Set pdfjob = Nothing
   
End Sub
0
 
LVL 12

Expert Comment

by:snailcat
ID: 34137762
Great
0
 

Author Closing Comment

by:mi_tuttle
ID: 34195039
Was able to solve using PDFCreator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question