?
Solved

Save Excel Worksheet with Primo PDF using Code

Posted on 2010-11-12
8
Medium Priority
?
1,437 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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.

771 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