Solved

Save Excel Worksheet with Primo PDF using Code

Posted on 2010-11-12
8
1,311 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Formatting code for Excel Forms 17 48
VBA: loop recent folder and copy txt file. 8 33
How to strip out formula but retain value in cell 2 23
Getting rid of #VALUE! 7 22
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

840 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