Using a macro to creat a PDF 1

[cadocs]

I am currently using excel to create emails in outlook express for you and I have gotten my VBA programs to copy data to other sheets and make those sheets active, but i want to see if I can have it automatically print a page to PDF.

I have Adobe acrobat 6.0 professional. Please help if you can...

Much thanks,

Geoff

 

[macropod]

Hi Geoff,

To do this, you'd need code to:
. select the "Acrobat PDFWriter" printer (this should be easy)
. find a way to tell Acrobat where and with what name to save the file (this would be the hard part). There may be information on how to go about this on one of the Acrobat forums/newsgroups.

Cheers

 

[GlennUK]

Hi macropod,

this is not as easy as you make it sound ( well, at least that what my user says, so blame him if all this is wrong ) ... according to rumour you need the Acrobat 6 SDK.

Here's a starting point:

http://partners.adobe.com/public/developer/acrobat/sdk/index.html

although I've been told it's not so easy to get to the point where you can actually download it.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[cadocs]

I have adobe 6.0 professional. I am also in the process of swithing to outlook 2003 instead of outlook express, so that i can have excel do more mail customizations than with outlook express.

I will check out the link and see what it says.

Thanks a ton for the help getting this started.

Geoff

 

[VhbuiA]

cadocs,

Please remember that if other users do not have Adobe PDF Writer installed in their personal machines, the code will most likely not work for them.

 

[cadocs]

Thanks guys, i actually got it to work and found a work around for the problem of windows choosing a different port (Ne04, Ne02, etc on each computer.


Public Function GetFullNetworkPrinterName() As String
' returns the full network printer name
' returns an empty string if the printer is not found
' e.g. GetFullNetworkPrinterName("printer_name")
' might return "printer_name on Ne04:"
Dim strCurrentPrinterName As String, strTempPrinterName As String, i As Long
strCurrentPrinterName = Application.ActivePrinter
i = 0
Do While i < 10
strTempPrinterName = "Adobe PDF on Ne" & Format(i, "00") & ":"
On Error Resume Next ' try to change to the network printer
Application.ActivePrinter = strTempPrinterName
On Error GoTo 0
If Application.ActivePrinter = strTempPrinterName Then
' the network printer was found
GetFullNetworkPrinterName = strTempPrinterName
i = 10 ' makes the loop end
End If
i = i + 1
Loop
' remove the line below if you want the function to change the active printer
' change back to the original printer
' Application.ActivePrinter = strCurrentPrinterName
'MsgBox (strTempPrinterName)
End Function





Dim PSFileName As String, PDFFileName As String, DistillerCall As String
Dim ReturnValue As Variant
'Define the path and filenames (can get the names from a cell, and add the path & extension):
PSFileName = "c:\" & target.Offset(0, -1).Value & " invoice.PS"
PDFFileName = "c:\" & target.Offset(0, -1).Value & " invoice.PDF"
'If the files already exist, delete them:
If Dir(PSFileName) <> "" Then Kill (PSFileName)
If Dir(PDFFileName) <> "" Then Kill (PDFFileName)
'The Sendkeys characters are the full path and filename, followed by the "Enter" key.
' These are buffered until the "print to file" screen appears:
SendKeys PSFileName & "{ENTER}", False
Application.ActivePrinter = GetFullNetworkPrinterName()
Worksheets("invoices").Range("a1:j29").PrintOut , PrintToFile:=True
'Add double quotes around the PS filename and PDF filename:
PSFileName = Chr(34) & PSFileName & Chr(34)
PDFFileName = Chr(34) & PDFFileName & Chr(34)
DistillerCall = "C:\Program Files\Adobe\Acrobat 6.0\Distillr\Acrodist.exe" & _
" /n /q /o" & PDFFileName & " " & PSFileName
'Call the Acrobat Distiller to distill the PS file. ReturnValue is zero
'if the application doesn't open correctly:
ReturnValue = Shell(DistillerCall, vbNormalFocus)
If ReturnValue = 0 Then MsgBox "Creation of " & PDFFileName & "failed."



Hope that helps everyone, and thanks to everyone that helped me out with any other issues.

Geoff

 

[PAULCALLAGHAN]

Check out a thread, dated October 14th, under Microsoft: Access Reports called "How to Email reports as PDF files", by mahaishan.

There is a link to code that will do what you want.

 

[swtrader]

cadocs' code for 'trapping' the printer setting saved me a lot of time and frustration in having to figure out how to get around the differing NeNN numbers on network printers when printing to a pdf file. I don't understand it, but it does work well.

swtrader
-- The trouble with doing something right the first time is that no one appreciates how hard it was.