Ok, thanks Dave. I'll try substituting "Excel" for "Word" in that code and
see what happens. STill don't know what to do about the reference library
though. Maybe it doesn't need one but I doubt it. Wait and see. Regards, Brett

"Dave Peterson" wrote:

I'm in the USA.

On this page: http://www.primopdf.com/primopdf_api.aspx

There's a box (top of the second column) that shows 3rd part applications and it
includes MSWord, Excel and Powerpoint.

And even though the code is written in a scripting language, it could be
modified for both MSWord and Excel (and any flavor of VBA???).


Brettjg wrote:

I was able to download the instruction manual, but it still doesn't even
mention Excel. I've sent Primo an email to ask if API will help. What country
are you in Dave?

"Dave Peterson" wrote:

That PrimoPDF API isn't something you can download from the site (at least I
didn't see it). And it doesn't come with the download of the primopdf printer
driver.

But you can request it. There's an email link at the bottom of this page:
http://www.primopdf.com/primopdf_api.aspx

That page says that the price is as low as $1.50 (USD??) per seat. But I bet
that's in bulk. I didn't see what a single seat license would be.



Brettjg wrote:

Hi Dave, yes I had noted that the code below was on the site, but I want run
this from Excel (not sure if I need to add anything to the sub name either).
I have the latest version of Primo because I only downloaded it yesterday.
The problem is a little more complex than just the pdf size. It may take a
bit of explanation so bear with me if you would. The code that I posted
before (from Ken Puls) works beautifully for a single sheet workbook.

I also need to run a macro to convert a multisheet workbbok to a pdf. When I
use the code that Ken has for this it doesn't release PDFCreator and
basically fails. I put in Ken's patch (to wait for a few seconds) and finally
got it to work by making it wait for 20 seconds, but the tricky part is that
the multi sheet workbook can have a variable number of worksheets. The sheets
may or may not be reduced in number because of different parameters set for
the particular client. So I'm guessing that the wait period has to be
variable, and that makes the code unreliable. I've posted the code I'm using
after the PRIMO code below.

Basically I'm wondering how to either patch Ken's code to make it reliable
and/or convert it so I can use Primo, which may not give the same error. Also
which library to reference in VBA for Primo (gsdll32.dll ??). I don't seem
to be able to tick any of the available files in the PRIMO folder that are
valid references.

Dave, as always your help is greatly appreciated, Regards, Brett.

Sub PRIMO_PRINT()
'Obtain local file path for later use
arrayScr = Split(WScript.ScriptFullName, "\", -1, 1)
For i = 0 To UBound(arrayScr) - 1
strPath = strPath & arrayScr(i) & "\"
Next

'File settings on file to create and file to convert
strPathToFiles = strPath
strFileToCreate = strPathToFiles & "W-Test.pdf"
strFileToConvert = strPathToFiles & "word.doc"

'Instantiate the PrimoPDF Object
Set APPrimo = CreateObject("PrimoAPI.Object")
APPrimo.UserString = "TG0Axxxxxxxxxx"
APPrimo.LicenseKey = "752xxxxxxxxxxxx119510012413123xxxxxxxxxx"

'Set the output location and name of the PDF to be created
APPrimo.OutputFile = strFileToCreate

'**PDF Quality Settings
'DPI resolution of created PDF
APPrimo.Resolution = 300
APPrimo.DebugLogging = True
APPrimo.CreatePrinter

'The code will also check and notify when the PDF file is created 'All below
code is provided "AS IS" and is not supported by activePDF
'Word Automation was tested with Microsoft Word 2003
'**Automate Word to print to the PrimoPDF Printer

Set WordObject = CreateObject("Word.Application")
WordObject.DisplayAlerts = False
Set NewDoc = WordObject.Documents.Open(strFileToConvert, False, True)
Set WordDialog = WordObject.Dialogs(97)

'After setting up PrimoAPI above, all we need to do is specify it as the
printer from
'the application which is creating the PDF
WordDialog.Printer = "PrimoAPI"
WordDialog.DoNotSetAsSysDefault = 1
WordDialog.Execute
NewDoc.PrintOut False
NewDoc.Close False
WordObject.Quit False

Set WordObject = Nothing
Set fso = Nothing
Set APPrimo = Nothing
End Sub

KEN PULS' CODE FOR MULTISHEET to a single PDF

Option Explicit
Sub PrintToPDF_MultiSheetToOne_Early()
Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String
Dim lSheet As Long
Dim lTtlSheets As Long

'/// Change the output file name here! ///
sPDFName = Range("pdf.name").Value
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
Set pdfjob = New PDFCreator.clsPDFCreator

If pdfjob.cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + _
vbOKOnly, "Error!"
Exit Sub
End If

With pdfjob
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0
.cClearCache
End With

lTtlSheets = Application.Sheets.Count
For lSheet = 1 To Application.Sheets.Count
On Error Resume Next 'To deal with chart sheets
If Not IsEmpty(Application.Sheets(lSheet).UsedRange) Then
Application.Sheets(lSheet).PrintOut copies:=1,
ActivePrinter:="PDFCreator"
Else
lTtlSheets = lTtlSheets - 1
End If
On Error GoTo 0
Next lSheet

Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
DoEvents
Loop

With pdfjob
.cCombineAll
.cPrinterStop = False
End With

Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
Application.Wait Now + TimeValue("0:0:5")

pdfjob.cClose
Set pdfjob = Nothing
End Sub

--

Dave Peterson


--

Dave Peterson