Hello out there

I'm using the following code from Ken Puls (thanks Ken) which works
fantastically well.
However, I note that a pdf created by PrimoPDF is only 2/3 of the size, and
just as sharp when printed. Does anyone know what reference library to use so
I can use Primo in the same code? There doesn't seem to be anything that
works when I add it from the Primo folder.

I'll bet Dave Petersen knows!

Regards, Brett

Option Explicit
Sub PrintToPDF_Early()
Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String
'OUTPUT FILE NAME
sPDFName = Range("pdf.name").Value
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator

If IsEmpty(ActiveSheet.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
.cClearCache
End With

ActiveSheet.PrintOut Copies:=1, ActivePrinter:="PDFCreator"
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False

Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
pdfjob.cClose
Set pdfjob = Nothing
End Sub

I bet he doesn't!

I've never used primopdf to print via vba.

There is a primopdf API guide at www.primopdf.com that includes some VBA sample
code to print from MSWord.

And you may want to get an updated version of the program (if you haven't
already).

Brettjg wrote:

Hello out there

I'm using the following code from Ken Puls (thanks Ken) which works
fantastically well.
However, I note that a pdf created by PrimoPDF is only 2/3 of the size, and
just as sharp when printed. Does anyone know what reference library to use so
I can use Primo in the same code? There doesn't seem to be anything that
works when I add it from the Primo folder.

I'll bet Dave Petersen knows!

Regards, Brett

Option Explicit
Sub PrintToPDF_Early()
Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String
'OUTPUT FILE NAME
sPDFName = Range("pdf.name").Value
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator

If IsEmpty(ActiveSheet.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
.cClearCache
End With

ActiveSheet.PrintOut Copies:=1, ActivePrinter:="PDFCreator"
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False

Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
pdfjob.cClose
Set pdfjob = Nothing
End Sub

--

Dave Peterson

And a direct link to the API stuff (not free):
http://www.primopdf.com/primopdf_api.aspx



Brettjg wrote:

Hello out there

I'm using the following code from Ken Puls (thanks Ken) which works
fantastically well.
However, I note that a pdf created by PrimoPDF is only 2/3 of the size, and
just as sharp when printed. Does anyone know what reference library to use so
I can use Primo in the same code? There doesn't seem to be anything that
works when I add it from the Primo folder.

I'll bet Dave Petersen knows!

Regards, Brett

Option Explicit
Sub PrintToPDF_Early()
Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String
'OUTPUT FILE NAME
sPDFName = Range("pdf.name").Value
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator

If IsEmpty(ActiveSheet.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
.cClearCache
End With

ActiveSheet.PrintOut Copies:=1, ActivePrinter:="PDFCreator"
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False

Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
pdfjob.cClose
Set pdfjob = Nothing
End Sub

--

Dave Peterson

Funny, I noticed the opposite, that PDFCreator's files were 1/2 the size (or
less) compared to PrimoPDF's files, each with the same quality. So when I
started to use Ken's code for automating PDF with PDFCreator, I switched to
PDFCreator for all of my PDF work.

I didn't realize PrimoPDF had much info about automation. Now I have to
decide whether it's worth checking it out.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Brettjg" wrote in message
...
Hello out there

I'm using the following code from Ken Puls (thanks Ken) which works
fantastically well.
However, I note that a pdf created by PrimoPDF is only 2/3 of the size,
and
just as sharp when printed. Does anyone know what reference library to use
so
I can use Primo in the same code? There doesn't seem to be anything that
works when I add it from the Primo folder.

I'll bet Dave Petersen knows!

Regards, Brett

Option Explicit
Sub PrintToPDF_Early()
Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String
'OUTPUT FILE NAME
sPDFName = Range("pdf.name").Value
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator

If IsEmpty(ActiveSheet.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
.cClearCache
End With

ActiveSheet.PrintOut Copies:=1, ActivePrinter:="PDFCreator"
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False

Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
pdfjob.cClose
Set pdfjob = Nothing
End Sub

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

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

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

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

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

Oh, I'm pretty sure you're going to need the API.

And that VBS code actually opened a new MSWord session. You won't need to do
that when you're running the code from within excel (when/if you get the API
file).

Brettjg wrote:

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


--

Dave Peterson