convert to pdf 3

[dodge20]

I have a form called coupons that is based off of a table. It contains 300+ records. Each record has a special code that I want to use for naming. So I want to create a seperate pdf file for each record based on that record's special code. For example:



Record 1 code: AA
File output would be AA.pdf



Record2 code: AB
File output would be AB.pdf



I would like to be able to do this in 1 step, not having to manually do it 300+ times. I don't know VBA, so go easy on me.



Dodge20

 

[jrbarnett]

You will need the Adobe PDF writer software installed, there is an FAQ here on programmatically manipulating the PDF writer software.

John

 

[dodge20]

Yeah, I read that, but it didn't make much sense. I was able to find something else, but I need a little help on looping so it will make multiple files. Also this is for a report, I was wondering if it could be done with a form.
Here it is

Save A Report As PDF faq703-2533

Dodge20

 

[jrbarnett]

Dodge20,

Shouldn't be that difficult to change it to a form, just change

DoCmd.OpenReport

to

DoCmd.OpenForm

in the SaveReportAsPDF function, then to loop through the output:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = Db.OpenRecordset ("Tablename")
Do While Not rs.EOF
 SaveReportAsPDF(rs!Code, "C:\Outputfiles\" & rs!Code)
 rs.MoveNext
Loop
rs.Close
Set db = Nothing

The word "Code" after the caution marks should be changed to the name of the field that holds the unique coupon codes.
The name of the table that the form is bound to should be placed between the quote marks where "Tablename" currently is located.
You will need to add a reference to the Microsoft DAO 3.6 Object library if using Access 2000, 2003 or XP, but 97 should be fine.

John

 

[dodge20]

I am getting a compile error in the line

SaveReportAsPDF(rs!Code, "C:\Outputfiles\" & rs!Code)

I may have it in the wrong spot, I will remind you I have no vba experience. This is what it looks like.

Public Sub SaveReportAsPDF(strReportName As String, strPath As String)

Dim strOldDefault As String

strOldDefault = QueryKey("Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device")

SetKeyValue "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", "Acrobat PDFWriter", REG_SZ

SetKeyValue "Software\Adobe\Acrobat PDFWriter", "PDFFilename", strPath, REG_SZ

SetKeyValue "Software\Adobe\Acrobat PDFWriter", "bExecViewer", 0, REG_SZ

DoCmd.OpenForm strReportName

SetKeyValue "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", strOldDefault, REG_SZ

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("gith")
Do While Not rs.EOF
SaveReportAsPDF(rs!code, "C:\outputfiles\" & rs!code)
rs.MoveNext
Loop
rs.Close
Set db = Nothing


End Sub

Dodge20

 

[dodge20]

Sorry I didn't say what the compile error was. It said Expected: =

Also I copied the wrong one over, this is what I actually have.

Public Sub SaveReportAsPDF(strReportName As String, strPath As String)

If Dir("C:\defaultprinter.bat") <> "defaultprinter.bat" Then
Call AddDefaultPrinterBat
sSleep (5000)
End If

Dim strOldDefault As String
Dim RetVal As Variant

strOldDefault = _
Left(QueryKey("Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device"), _
InStr(1, QueryKey("Software\Microsoft\Windows NT\CurrentVersion\Windows", _
"Device"), ",") - 1)

RetVal = Shell("C:\defaultprinter.bat " & Chr(34) & "Acrobat PDFWriter" & Chr(34), vbMinimizedNoFocus)

'SetKeyValue "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", "Acrobat PDFWriter,winspool,LPT1:", REG_SZ

SetKeyValue "Software\Adobe\Acrobat PDFWriter", "PDFFilename", strPath, REG_SZ

SetKeyValue "Software\Adobe\Acrobat PDFWriter", "bExecViewer", 0, REG_SZ

sSleep (5000)

DoCmd.OpenForm strReportName

RetVal = Shell("C:\defaultprinter.bat " & Chr(34) & strOldDefault & Chr(34), vbMinimizedNoFocus)

'SetKeyValue "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", strOldDefault, REG_SZ

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("gith")
Do While Not rs.EOF
SaveReportAsPDF(rs!code, "C:\Outputfiles\" & rs!code)
rs.MoveNext
Loop
rs.Close
Set db = Nothing


End Sub

Dodge20

 

[jrbarnett]

Dodge20

The "SaveReportAsPDF" sub is a standalone sub that does the work for you. My code earlier calls the sub, looping through records in your table, outputting each to a separate PDF file.
By putting the loop code in the function, you are creating recursion, where the function calls itself. This will cause problems for other things.

To use my code, you should put it behind a command button, menu object or similar and then activate it to trigger the code export.

John

 

[dodge20]

I still get the error message
Compile error Expected: =

on this line.

SaveReportAsPDF(rs!Code, "C:\Outputfiles\" & rs!Code)


Dodge20

 

[oharab]

Change the line to:

SaveReportAsPDF rs!Code, "C:\Outputfiles\" & rs!Code

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;

NoFX-The Decline
----------------------------------------------

 

[dodge20]

I am now getting an error message that says

The form name 'AN' is misspelled or refers to a form that doesn't exist.

AN is a value in the field code. It isn't the name of the form. The form name is Coupons

Any ideas?

Dodge20

 

[CalMyPal]

May I suggest you just go to

http://www.pdf995.com

and download their program. All you do is go to print and select pdf995 instead of you printer and it will produce a pdf file.
If you want to buy it, it's $9.95.

 

[jrbarnett]

CalMyPal

This software may be incredibly good value for money compared to the Adobe equivalent, but it won't help the automatic production of over 300 PDF files, one per record, which is the problem that Dodge20 needs to solve.

John

 

[dodge20]

Do you think I need another string value such as

Public Sub SaveReportAsPDF(strReportName As String, strPath As String, strReportField As String)

Then adjust Johns code because it seems to be looking for the form named after the code field. When the name of the form is Coupons.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = Db.OpenRecordset ("Tablename")
Do While Not rs.EOF
 SaveReportAsPDF(rs!Code, "C:\Outputfiles\" & rs!Code)
 rs.MoveNext
Loop
rs.Close
Set db = Nothing

Dodge20

 

[dodge20]

Does anyone have any suggestions? Or is this just not do-able?

Dodge20

 

[oharab]

I can see your problem.
This code

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = Db.OpenRecordset ("Tablename")
Do While Not rs.EOF
 SaveReportAsPDF(rs!Code, "C:\Outputfiles\" & rs!Code)
 rs.MoveNext
Loop
rs.Close
Set db = Nothing

runs the SaveReportAsPDF each loop with different values ie:

SaveReportAsPDF("AA", "C:\Outputfiles\AA")
then
SaveReportAsPDF("AB", "C:\Outputfiles\AB")
then
SaveReportAsPDF("AC", "C:\Outputfiles\AC")

assuming the table tablename contains
AA
AB
AC

The function given by John takes 2 arguments:
strReportName: The name of the Access form you are saving (since you modified it)
strPath: Then name of the pdf you are trying to create.

Nowhere in the code are you specifying which record to print out.

You need to change the SaveAsPDF code so that it selects the record you are trying to print.

Public Sub SaveFormAsPDF(strFormName As String, strPath As String, strFilter AS String)

    If Dir("C:\defaultprinter.bat") <> "defaultprinter.bat" Then
        Call AddDefaultPrinterBat
        sSleep (5000)
    End If

    Dim strOldDefault As String
    Dim RetVal As Variant
    
    strOldDefault = _
        Left(QueryKey("Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device"), _
        InStr(1, QueryKey("Software\Microsoft\Windows NT\CurrentVersion\Windows", _
        "Device"), ",") - 1)
    
    RetVal = Shell("C:\defaultprinter.bat " & Chr(34) & "Acrobat PDFWriter" & Chr(34), vbMinimizedNoFocus)
    
    'SetKeyValue "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", "Acrobat PDFWriter,winspool,LPT1:", REG_SZ
    
    SetKeyValue "Software\Adobe\Acrobat PDFWriter", "PDFFilename", strPath, REG_SZ
    
    SetKeyValue "Software\Adobe\Acrobat PDFWriter", "bExecViewer", 0, REG_SZ

    sSleep (5000)

    DoCmd.OpenForm strFormName,,strFilter 'Open Form with Filter
    Docmd.Printout 'Print it
    DoCmd.Close acForm, strFormName 'Close it

    RetVal = Shell("C:\defaultprinter.bat " & Chr(34) & strOldDefault & Chr(34), vbMinimizedNoFocus)
    
    'SetKeyValue "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", strOldDefault, REG_SZ

End Sub

Save all of this in the same place you saved all the code in FAQ703-2533 (you did save all the code from that FAQ in a module didn't you!)
Now you need to change the line
SaveReportAsPDF(rs!Code, "C:\Outputfiles\" & rs!Code)
to
SaveFormAsPDF("frmYOURFORMGOESHERE", "C:\Outputfiles\" & rs!Code & ".pdf","Code='" & rs!Code & "'")

I've not checked anything as I don't have access on this PC, but you should be getting somewhere like.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;

NoFX-The Decline
----------------------------------------------

 

[dodge20]

So close. What this does, is it prints all 334 records in a file called AA.pdf, then prints all 334 records again in a file called AB.pdf. It then continues to do this the full 334 times. I want it to just print the individual record in each file.

Stars all around once this works.

Dodge20

 

[dodge20]

Also, I should note that I took out the ssleep (5000) because I was getting an error message. Is that a big deal?

Dodge20

 

[PHV]

Replace this:
DoCmd.OpenForm strFormName,,strFilter 'Open Form with Filter
By this:
DoCmd.OpenForm strFormName,,,strFilter 'Open Form with Condition

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[dodge20]

Thanks guys, this is going to save me a lot of time. Everything looks like it is working fine.

Dodge20

 

[kmclane]

I have been trying to get this to work for me. I am using this for a report. I don't need all the registry stuff, as I have my report set to print to a pdf always, and I will be the only one to run this. All I need is to loop through my query and open and print the report for each record based on the field "name1" using the same value as the file name. I have pared the SavereportasPDF to this:

Public Sub SaveReportAsPDF(strReportName As String, strFilter As String)
DoCmd.OpenReport strReportName, , , strFilter 'Open Report with Condition
DoCmd.PrintOut 'Print it
DoCmd.Close acReport, strReportName 'Close it
End Sub

And have this code in a public funtion:

Public Function PrintAgntSettleRep()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("TotalSettleAgent")
Do While Not rs.EOF
SaveReportAsPDF "SettlementReport", "C:\Agentfiles\" & rs!Name1 & ".pdf", "name1='" & rs!Name1 & "'"
rs.MoveNext
Loop
rs.Close
Set db = Nothing
End Function

For testing I am calling it from a macro, but I am not even getting the correct file name at this point. Any help would be appreciated.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg