Macro to print pdf and insert file name

[caradawn]

Hey all -

I am trying to write a macro that will print a worksheet to PDF and automaically name the file. This is the code I have so far:

Option Explicit

Private Sub PrintToPDF_Click()

Dim CurrentPrinter As String
Dim fileName As String

Let fileName = Cells(7, "e")

'Sets the sheet to print out in black and white
ActiveSheet.PageSetup.BlackAndWhite = True

CurrentPrinter = Application.ActivePrinter ' save the currently active printer
On Error Resume Next ' ignore errors
Application.ActivePrinter = "Adobe PDF on Ne05:" ' change to PDF printer
SendKeys fileName & "{ENTER}" ', False
ActiveSheet.PrintOut ' print the active sheet (Network Funding)
Application.ActivePrinter = CurrentPrinter ' change back to the original printer
On Error GoTo 0 ' resume normal error handling
End Sub

The problem is the sendkeys function does nothing and I still have to manually input the file name. Can anyone please help?!?!

Thanks -
Cara

 

[BrianB]

Whatever method we use requires some sort of additional software or driver to do the conversion. It is a very complicated business.

This seems interesting because the driver is free.

http://www.excelguru.ca/node/21

 

[caradawn]

I have actually already tried use this code. I was unable to download pdfcreator from the suggested site so I was looking for another solution. If you know of another site to download a free copy of pdf creator I will try this code.

Thanks for your response and help.
Cara

 

[BrianB]

I have just followed the links and downloaded the file without problem.
I suggest you have another go -you may have tried at a bad time.

 

[caradawn]

When I try to downloand PDFCreator I get this error message at the end of the installation (Setup Wizard part):
Runtime Error (at 110:383):
Access violation at address 7C928COB. Read of address 3D524559.

Any ideas?

 

[BrianB]

Perhaps you need to temporarily switch your firewall off - and/or antivirus.
You have not said what you are doing or how far you get into the process.

I got no setup wizard. Just downloaded 2 files and double clicked
pdf995s.exe & ps2pdf995.exe to run them - in fact nothing seemed to happen. Check an application to see if the 'printer' has appeared.

Try saving the files to C drive & run from there