Solved

Save Excel Worksheet with Primo PDF using Code

Posted on 2010-11-12
8
1,227 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

759 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

21 Experts available now in Live!

Get 1:1 Help Now