Convert Excel to .ps file 1

[jskang]

Does anybody know how to write a VBA excel program for converting excel to postscript file.

Many thanks

JSK

 

[fizzak]

Wouldn't you just Print To File from Excel?

 

[Eggles]

>>Wouldn't you just Print To File from Excel?<<

...of course - this would be after choosing a postscript printer (preferably Distiller) from the printer menu.

And usually, MS programs like Excel do not create a postscript file labelled with a PS extension, but with a PRN extension.

 

[ctarr]

JSK, You can create a PS printer on your machine and have the VBA print to this printer, please see my response to thread181-27567

Craig

 

[jskang]

Ctarr,

I check your response and I don't understand. Is this the correct thread?

JSK

 

[ctarr]

JSK, sorry I copied the wrong thread, try this one. thread223-548368.

There are a couple of other posts in this same thread that talk about how to do this with VB or VBA if you don't want to use the postscript printer.

Craig

 

[smeaggie]

I once wrote an excel macro wich goes through all cells in the current print area and analyzes the data in it. it had support for colored text, left / center / right aligned text and borders. however far from perfect it worked. but I would recommend using the "print to file" method

greetings,
Smeaggie

 

[smeaggie]

ps: forgot to mention the macro wrote a postscript file

 

[sabloomer]

ctarr or smeaggie,

May I ask a quick question? I am trying to combine multiple Access reports into one PDF. I found the Rundirex file that the Distiller uses, and I think I can use that. My problem is that the Distiller requires my files to be PostScript files to combine them. I set up a PostScript printer and I can make the needed files, but I have to enter the path and file name each time. I need to automate this process, but I can not figure a way to configure the printer not to ask for a file location, or write Access VBA code to export the file as a PostScript file.

Any help would be wonderful,

sabloomer

 

[ctarr]

sabloomer, I don't think you can setup the printer to make a new file name each time.

If you change the preferences in Distiller to not ask for a destination it will just print to the location (port) of the postscript file. (Open Distiller and click on File | Preferences, uncheck the box for ask for pdf file destination).

My postscript printer prints to the port, c:\myfolder\file.ps. My VB program prints the document (which could be an access report) to this printer. Which creates file.ps. Before I print the next document I have a function in VB which renames file.ps to the document name (or could be report title in Access).

Then the program opens the next document, prints to file.ps and so on.

I convert around 700+ pages every week with this VB application. It saves hours of manually printing and collating documents.

Craig

 

[sabloomer]

Ctarr,

Thanks for the help. I have done nothing but work on this issue the last three days. Can you please explain how you setup your printer so it prints the file as c:\myfolder\file.ps every time? If I can do that then I can clear my last hurdle.

Here is what I have done so far. I have a printer port setup with the distiller driver. It is configured to not ask for a name, but it produces *.pdf files. I have a printer set to "file" port with a MS Publisher Imagesetter driver. It produces the needed *.ps files, but always prompts me for a name.

Thanks,

sabloomer

 

[ctarr]

sablommer,
as stated in my other thread, Thread223-548368

Install a new local printer, and choose to use a driver like the "Apple LaserWriter Pro" (good PS print driver). Don't check the "Print to File" box. But rather point the port to "C:\some directory\Postscript.ps" (you may have to "Create" a new port).

Of course, some directory is what ever path you want and Postscript.ps can be any file name.

When you print to this printer it will make a PS file with the name Postscript.ps. You'll just have rename the file after each print to avoid replacing the previous print job.

Just make sure the file you end up with has the ps extension. Using this print driver will give you a usable PS file.

Craig

 

[sabloomer]

ctarr,

That worked. Thank you very much! Sorry to be a pest. I remember reading that post, but I kept focusing on the code samples provided. Anyway, I think I have all the parts, so it is time to put them all to gether.

sabloomer