Solved

Save Excel Worksheet with Primo PDF using Code

Posted on 2010-11-12
8
1,257 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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.

947 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now