Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 12th, 2004, 10:40 AM
Registered User
 
Join Date: Feb 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Excel 2000 Create PDF with VBA

Hello,
I now there was much entries over this theme.
I selected the way over Distiller and PS-File.
The Code see below or in Forum
http://p2p.wrox.com/archive/vba_excel/2002-11/37.asp

I activated the Distiller library
and checked off the "Do not send fonts to Distiller" option.
It works correct until it comes to the Line
    Set myPDF = New PdfDistiller
then it breaks with error 429 -can not creat object through Active-X component - or like that. (I do not have the original english message syntax - only in German language)
I activated following additional Libraries:
- VB for applications
- MS Excel 9.0 Object Library
- MS Forms 2.0 - " -
- MS Office 9.0 - " -
- MS DAO 3.6 - " -
- MS Outlook 9.0 - " -
- OLE Automation and last but not least
- Acrobat Distiller
My Versions are Excel 2000 and Acrobat 5.0
I have no more ideas to solve that problem.
Can somebody help me? Thanks!

'***********************************************
' Define the postscript and .pdf file names.
Dim PSFileName As String
Dim PDFFileName As String
PSFileName = "d:\temp\myPostScript.ps"
PDFFileName = "d:\temp\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As Worksheet
Set MySheet = ActiveSheet

MySheet.PrintOut copies:=1, preview:=False, _
ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True, _
prtofilename:=PSFileName

' Convert the postscript file to .pdf

Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""
***
 
Old May 21st, 2004, 05:30 AM
Registered User
 
Join Date: May 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I tried this code but I can only run with administrator permissions. Without these permissions I get the following error:
“ActiveX component can't create object or return reference to this object (Error 429)”

Could you give me some tip?
Thanks in advance
Best regards





Quote:
quote:Originally posted by ralf.schmitt@rsdv.de
 Hello,
I now there was much entries over this theme.
I selected the way over Distiller and PS-File.
The Code see below or in Forum
http://p2p.wrox.com/archive/vba_excel/2002-11/37.asp

I activated the Distiller library
and checked off the "Do not send fonts to Distiller" option.
It works correct until it comes to the Line
    Set myPDF = New PdfDistiller
then it breaks with error 429 -can not creat object through Active-X component - or like that. (I do not have the original english message syntax - only in German language)
I activated following additional Libraries:
- VB for applications
- MS Excel 9.0 Object Library
- MS Forms 2.0 - " -
- MS Office 9.0 - " -
- MS DAO 3.6 - " -
- MS Outlook 9.0 - " -
- OLE Automation and last but not least
- Acrobat Distiller
My Versions are Excel 2000 and Acrobat 5.0
I have no more ideas to solve that problem.
Can somebody help me? Thanks!

'***********************************************
' Define the postscript and .pdf file names.
Dim PSFileName As String
Dim PDFFileName As String
PSFileName = "d:\temp\myPostScript.ps"
PDFFileName = "d:\temp\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As Worksheet
Set MySheet = ActiveSheet

MySheet.PrintOut copies:=1, preview:=False, _
ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True, _
prtofilename:=PSFileName

' Convert the postscript file to .pdf

Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""
***
 
Old June 10th, 2004, 09:44 AM
Registered User
 
Join Date: Jun 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Under Windows XP (I suppose Windows 2000 too) you need to be Administrator of your PC to use Distiller API.

Power users and simple users receive the error 429 "ActiveX Component Can't Create Object"



 
Old July 8th, 2004, 04:38 AM
Registered User
 
Join Date: Jul 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have the same problem with Windows NT.
It's only work with administrator permissions.

Do you know which permissions I have to set (maybe on database register) on simple users profil to don't have the 429 error with API Distiller ?

First, is it possible ?

Thanks in advance.
 
Old October 5th, 2004, 09:08 PM
Registered User
 
Join Date: Oct 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi All,

I use Win2K, not local admin and this code works fine.

However, I'm embedding it into a loop to print 23 XL books (active sheet only) and I get the following error on sheet 2;

Method 'FileToPDF' of object 'IPdfDistller' failed.

Anyone else had this? Individually the code works fine, but you'd might as well print each sheet manually if that were the case!

I'm guessing that at the Distiller buffer needs to be empty before attempting to print the next file, anyone have any ideas?

I've added a code to tag the PS file with the loop counter, creating 23 PS files as a start to debug, but I don't really understand the error message so it's a bit 'stab in the dark' until I can find some documentation on this.

Cheers,

Jon



 
Old January 4th, 2005, 08:25 PM
Registered User
 
Join Date: Jan 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi JonTout,

I am trying to write a script that sounds nearly identical to yours. I am working on a VBA script to generate PDFs from all of the worksheets in an Excel file, without User Interaction. I use Win2K as well, but I cannot even convert the first Excel sheet to PDF. I have Administrator priviledges.

When I run the script listed above, the script halts at this line:
"Dim myPDF As PdfDistiller"
The error states:
"Compile error: User-defined type not defined"

You said that you were able to generate the first page. Could you post your script online so that I could compare it to mine and see how you were at least able to get 1 page out of it.

Much thanks in advance!

Gary



Quote:
quote:Originally posted by JonTout
 Hi All,

I use Win2K, not local admin and this code works fine.

However, I'm embedding it into a loop to print 23 XL books (active sheet only) and I get the following error on sheet 2;

Method 'FileToPDF' of object 'IPdfDistller' failed.

Anyone else had this? Individually the code works fine, but you'd might as well print each sheet manually if that were the case!

I'm guessing that at the Distiller buffer needs to be empty before attempting to print the next file, anyone have any ideas?

I've added a code to tag the PS file with the loop counter, creating 23 PS files as a start to debug, but I don't really understand the error message so it's a bit 'stab in the dark' until I can find some documentation on this.

Cheers,

Jon



 
Old January 7th, 2005, 04:53 AM
Registered User
 
Join Date: Jan 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello,
can you tell me how I could use the third parameter in the command:
myPDF.FileToPDF PSFileName, PDFFileName, ""

I try to find a way to preserve my URLs in the excel file and transfer them into the pdf without manually recreating them in Acrobat. The only way that seems to work is via the Acrobat menu in Excel, but then I have to input the file name which doesn't work for batch processes.

Thanks
Frank

Quote:
quote:Originally posted by ralf.schmitt@rsdv.de
 Hello,
I now there was much entries over this theme.
I selected the way over Distiller and PS-File.
The Code see below or in Forum
http://p2p.wrox.com/archive/vba_excel/2002-11/37.asp

I activated the Distiller library
and checked off the "Do not send fonts to Distiller" option.
It works correct until it comes to the Line
    Set myPDF = New PdfDistiller
then it breaks with error 429 -can not creat object through Active-X component - or like that. (I do not have the original english message syntax - only in German language)
I activated following additional Libraries:
- VB for applications
- MS Excel 9.0 Object Library
- MS Forms 2.0 - " -
- MS Office 9.0 - " -
- MS DAO 3.6 - " -
- MS Outlook 9.0 - " -
- OLE Automation and last but not least
- Acrobat Distiller
My Versions are Excel 2000 and Acrobat 5.0
I have no more ideas to solve that problem.
Can somebody help me? Thanks!

'***********************************************
' Define the postscript and .pdf file names.
Dim PSFileName As String
Dim PDFFileName As String
PSFileName = "d:\temp\myPostScript.ps"
PDFFileName = "d:\temp\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As Worksheet
Set MySheet = ActiveSheet

MySheet.PrintOut copies:=1, preview:=False, _
ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True, _
prtofilename:=PSFileName

' Convert the postscript file to .pdf

Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""
***
 
Old January 12th, 2005, 02:04 PM
Registered User
 
Join Date: Jan 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi there,

This problem is very annoying!!!!!

To solve take "Set myPDF = New PdfDistiller" out of the Loop so it is called once. Also put "DoEvents" before and after the conversion line ("myPDF.FiletoPDF PSFileName, PDFFileName, "") to clear the stack.

Distiller sucks. oh well.





 
Old March 5th, 2008, 03:36 PM
Registered User
 
Join Date: Mar 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi

I wanted to print custom views to pdf and used the hints above and the following to get it to work. It worked just great - I used it to put out about 100 pdf file 4 times.

 - Create some custom views.
 - Put in references in the VBA to Acrobat reader
 - Need adobe writer 7 or 8
 - Go to system printers and right click ‘Adobe PDF’ , select ‘Printer Preferences’, uncheck ‘Rely on system fonts only, do not use document fonts’


Put the following code in the module
Sub printpdfs1()
 Dim path1 As String
 Dim model1 As String

 Dim filename As String

 path1 = GetCostModelPath()
 model1 = "d"

  filename = path1 & "Tables\ps\table18" & model1 & "_p1.ps"
  Call Printpdfs2("CapDepr_p1", filename)
  filename = path1 & "Tables\ps\table18" & model1 & "_p2.ps"
  Call Printpdfs2("CapDepr_p2", filename)
  filename = path1 & "Tables\ps\table18" & model1 & "_p3.ps"
  Call Printpdfs2("CapDepr_p3", filename)
  filename = path1 & "Tables\ps\table18" & model1 & "_p4.ps"
  Call Printpdfs2("CapDepr_p4", filename)
  filename = path1 & "Tables\ps\table20" & model1 & "_p1.ps"
  Call Printpdfs2("EstOwnOprCost_p1", filename)

End Sub

Function Printpdfs2(ShNameView As String, PSFileName As String)
' Define the postscript and .pdf file names.
' Print the Excel range to the postscript file
'put references in excel to adobe and acrobat reader
'go control panel> printers>right click on ADOBE and then desect the automatically use fonts options

Application.ScreenUpdating = False
ActiveWorkbook.CustomViews(ShNameView).Show

Dim mysheet As Worksheet

ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False, _
ActivePrinter:="Adobe PDF", printtofile:=True, collate:=True, _
prtofilename:=PSFileName

' Convert the postscript file to .pdf

Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
Dim PDFFileName As String
PDFFileName = Mid(PSFileName, 1, Len(PSFileName) - 2) & "pdf"

myPDF.FileToPDF PSFileName, PDFFileName, ""
'***

End Function

Function GetCostModelPath() As String
'Returns the current place where the model is
'needs to be set manually

GetCostModelPath = "C:\projs\costs_V2\"
End Function








Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA to create PDF from webpage ncarty97 Excel VBA 0 September 25th, 2006 02:48 PM
Excel 2000 VBA Problems drsammyb Excel VBA 0 January 17th, 2005 11:41 AM
VBA for Excel 2000...HELP! lhardesty Excel VBA 2 August 20th, 2004 09:08 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.