excel file to pdf

[Chrissy]

Hello,

I have a program created in VB6 that creates an Excel spreadsheet and i want to save it as a .pdf instead of a .xls file.

I have tried multiple ways of coding it but nothing seams to work. Below is the way that seems closest to working...

Dim p As String
Dim myPDF As PdfDistiller
Dim xBook As String  'Contains name of createad workbook
    
p = "Acrobat Distiller on Ne00:"
    
xApp.Workbooks(xBook).PrintOut ActivePrinter:=p, printtofile:=True, prToFileName:="c:\mypostscript.ps"
 
Set myPDF = New PdfDistiller
myPDF.FileToPDF "c:\mypostscript.ps", "G:\MVBS\Final mvbs\" & Format(Info.xYear, "0000") & "\" & Format(rsProp!entity_num) & ".pdf", ""
Set myPDF = Nothing

It just creates a .log file with the following errors in it...

%%[ Error: undefined; OffendingCommand: E*t600R&u600D*r0F&l0o&l2A&l7H&l0S&l1X&l8c1E*p0x0Y*c0t5760x7680Y*b0M ]%%
%%[ Flushing: rest of job (to end-of-file) will be ignored ]%%
%%[ Warning: PostScript error. No PDF file produced. ] %%

I'm not sure what is going wrong or how to accomplish what I'm trying to do. I have read several posts and a lot have similar code, but I can't get it to work. Maybe I am missing a particular reference??

Any help is appreciated.

Thanks!

[westconn1]

Warning: PostScript error.

is the file c:\mypostscript.ps created correctly and readable?
i am not sure you can specify the file type for printtofile, i believe the default was .prn, which would not be a postscript file
if you are using distiller to render the postscript file then you should not use excels printtofile parameter, and can not specify the file name as a parameter for the printout method

i have not used distiller, but some other pdf printer drivers have options to set file name and print to pdf without user input, i would presume that distiller also has such options, try reading the documentation

probably you should avoid writing to the root directory of drive c:

[Chrissy]

When i open the postscript file, there is stuff in there... whether it is correct or not I do not know. I'm sure I am missing something important but can't really find any information for what details i actually need. I will try looking for info on the distiller.

Thanks for the advice.

[DataMiser]

The easy way is to install a PDF print driver and simply print using that printer.

[Chrissy]

i already have the capability to print to pdf's on my machine.. how does that look in code?

[DataMiser]

Simply set the Printer object to point to the pdf printer then call the print method of the printer object.

[Chrissy]

If i did it that way, the user would have to enter the file name every time a file is created. The program will be creating about 100 files. This is not an option. Unless you know of a way to programmatically save the file without prompting the user.

[DataMiser]

I think you should be able to specify the file name as part of the printer properties but have not looked into it myself.

[Chrissy]

If you add a file name it is expecting it in postscript which leads me back to my original problem.

[westconn1]

Unless you know of a way to programmatically save the file without prompting the user.

but some other pdf printer drivers have options to set file name and print to pdf without user input,

this can be done with some pdf printer drivers. which one ore you using?
generally it requires using code to change registry settings to save without user input and the next filename\path to save to

this should indeed be quite possible to do using distiller, but i have no experience with using distiller, i believe using the print to file option is incorrect, read the documentation for distiller

if you wish to try some other pdf printer driver, you can download the vb source code and executable for pdfcreator from sourceforge, with examples on usage, and there are some other free pdf drivers that have examples on automating from any programming or scripting code