VBA - Adobe Form PrintToFile .pdf

[llRobRoyll]

Hey guys,

got a little issue right here and can't solve it...

My intended goal is:
by using VBA (through Excel) I am accessing an adobe form, fill it with certain data from an excel-sheet and print it to a .pdf-file. Filling the form with data and printing it to a pdf-file is repeated by a loop until the excel-list is finished. Then, the form ist closed w/o saved and all objects are set to "nothing".

My issue:
Everything works out fine. But the print won't work. I just don't get the code correctly. I am used to VBA in an office environment, but not to handling adobe. I tried different solutions. Simple printtofile - commands didn't work. When using the Adobe Distiller I am getting an error.

PLEASE: Can you provide me with a correction or a better solution to reach my goal??

My code so far:
Public Sub PRINTtheDOC()

Dim gApp As Acrobat.CAcroApp
Dim AvDoc As Acrobat.CAcroAVDoc
Dim gPDDoc As Acrobat.CAcroPDDoc
Dim pdfDIST As PdfDistiller 'Alternatively I can Dim PDFDistiller6, also

Dim FormApp As AFORMAUTLib.AFormApp
Dim AcroForm As AFORMAUTLib.Fields
Dim Field As AFORMAUTLib.Field

Dim x As Boolean

Const DOC_FOLDER As String = "#SERVERFOLDER\...\#PRGFOLDER"

Set gApp = CreateObject("AcroExch.App")
Set gPDDoc = CreateObject("AcroExch.PDDoc")
Set AvDoc = CreateObject("AcroExch.AVDoc")
'Set pdfDIST = New Distiller 'When I am trying to use the Distiller, the right here I get the ERROR: Runtime Error 429 - ActiveX Component Can't Create Object --- why? I referred to the distiller

x = AvDoc.Open(DOC_FOLDER & "\TEST.pdf", "")

AvDoc.Maximize (1) '(Want to watch the prog fillig the doc - removing it later)

Set FormApp = CreateObject("AFormAut.App")

'Do until "#ECXEL-LIST" is done ...

For Each Field In FormApp.Fields
If Field.Name = "Name" Then
Field.Value = "XXXXX"
End If
'a.s.o. for all available fields ...
Next

' FIRST TRY: JUST SAVE THE FORM TO A REGULAR PDF - Won't work due to a wrong command maybe ?!
' x = AvDoc.Save(PDSaveFull, DOC_FOLDER & "\TEST2.pdf")

' SECOND TRY: USING DISTILLER - Won't work due to the error above - so don't get to check the code following, which may also be wrong
pdfDIST.FileToPDF AvDoc, DOC_FOLDER & "\TEST2.pdf", ""

'Loop

AcroApp.Exit
Set gApp = Nothing
Set gPDDoc = Nothing
Set AvDoc = Nothing

End Sub

SORRY again, I am just not used to handling Adobe Objects by VBA - which I am bound to in this project.

References set: (shortly named, focussing on main references for this issue)
- VBA
- MS Excel 14 OLB (and more ... )
- Acrobat Access 3 TLB
- Acrobat 9 TLB
- AFormAut 1 TLB
- Acrobat Distiller
- AdobePDFMakerForOffice
- AdobePDFMakerX

CAN ANYONE HELP PLEEEEEEEEEASE? MANY THX !!!

 

[genomon]

You are making it way too hard. Download print to pdf add-in from MS; code is only 4 or 5 lines. Details here:

http://www.excelguru.ca/content.php?170-Print-To-PDF-Using-Microsoft-s-PDF-XPS-Add-in

Beir bua agus beannacht!

 

[llRobRoyll]

Hi genomon, many thx for your advice. in my opinion this won't work. I am not trying to convert an excel file to pdf but to fill an existing pdf-form with data several times an after each completion saving it on the server in a loop. either, the code you referred to won't work or i don't know how you would use it to reach the goal. many thx for a further advice, m8.
ps.: i cannot skip the pdf-form ... think about it as an original contract with open blanks, which is repeatedly filled with data, printed and saved on the server as a pdf for further purpose.