Print report to Acrobat PDF (AccessXP)

Topic

New Reply

I am trying to write some code to print an Access report to an adobe acrobat file. How do you specify the printer in Access VBA?

Reply | Quote

Replies

A simple alternative to writing VBA code to select a printer is to format the report for a specific printer.
Open the report in Design view and go to page setup.

If you sometimes want to print and sometimes want to convert to pdf, you can have two copies of the report, with one set for the default printer and one for acrobat.

Reply | Quote

A simple alternative to writing VBA code to select a printer is to format the report for a specific printer.
Open the report in Design view and go to page setup.

If you sometimes want to print and sometimes want to convert to pdf, you can have two copies of the report, with one set for the default printer and one for acrobat.

Reply | Quote

Look up the Printer object and property and the Printers collection in the online help, and look up the name of the “printer” for Acrobat (I don’t have that myself, so I don’t know the name)

Examples:

Me.Printer = Application.Printers(“Acrobat”)

where Me refers to the report, if the code is in the report module, or

Reports!MyReport.Printer = Application.Printers(“Acrobat”)

where MyReport is an open report.

Reply | Quote

Thanks Hans. That worked. Now I have one more problem. After setting the printer, I cannot seem to save the report to the PDF file.

a) If I use Docmd.OutputTo, I can only save it in one of the Microsoft formats (eg RTF, etc..) , which corrupts the PDF file.
If I use Docmd.Printout, this doesn’t let me specify the destination.

What is the best way to print to an acrobat file, in a specific location?

Reply | Quote

I believe it involves setting the filename in a Registry entry before printing the report (using PrintOut, not OutputTo). You can copy the code below into a standard module, then use

SetPDFFileName “C:AccessTest.pdf”

You might also use the PDF and Mail library for Access from ACG Soft. It’s not free, though.

Option Compare Database
Option Explicit

Private Declare Function RegOpenKeyA Lib “advapi32.dll” _
(ByVal HKEY As Long, ByVal sSubKey As String, ByRef hkeyResult As Long) As Long
Private Declare Function RegCloseKey Lib “advapi32.dll” _
(ByVal HKEY As Long) As Long
Private Declare Function RegSetValueExA Lib “advapi32.dll” _
(ByVal HKEY As Long, ByVal sValueName As String, _
ByVal dwReserved As Long, ByVal dwType As Long, _
ByVal sValue As String, ByVal dwSize As Long) As Long
Private Declare Function RegCreateKeyA Lib “advapi32.dll” _
(ByVal HKEY As Long, ByVal sSubKey As String, ByRef hkeyResult As Long) As Long

Public Enum HKEY_Enum
HKEY_CLASSES_ROOT = &H80000000
HKEY_CURRENT_USER = &H80000001
HKEY_LOCAL_MACHINE = &H80000002
HKEY_USERS = &H80000003
HKEY_CURRENT_CONFIG = &H80000004
HKEY_DYN_DATA = &H80000005
End Enum

Public Function SetRegValue(ByVal HKEY As HKEY_Enum, ByVal Path As String, _
ByVal Entry As String, ByVal Value As String) As Boolean

‘ Set text value in registry
Dim lngKey As Long
On Error GoTo Proc_Error

If RegOpenKeyA(HKEY, Path, lngKey) 0 Then
RegCreateKeyA HKEY, Path, lngKey
End If
SetRegValue = (RegSetValueExA(lngKey, Entry, 0&, 1&, Value, Len(Value) + 1) = 0)
RegCloseKey lngKey
Exit Function

Proc_Error:
MsgBox Err.Description, vbCritical
End Function

Public Sub SetPDFFileName(sPath As String)
SetRegValue HKEY_CURRENT_USER, “SoftwareAdobeAcrobat PDFWriter”, _
“PDFFileName”, sPath
End Sub

Reply | Quote

I believe it involves setting the filename in a Registry entry before printing the report (using PrintOut, not OutputTo). You can copy the code below into a standard module, then use

SetPDFFileName “C:AccessTest.pdf”

You might also use the PDF and Mail library for Access from ACG Soft. It’s not free, though.

Option Compare Database
Option Explicit

Private Declare Function RegOpenKeyA Lib “advapi32.dll” _
(ByVal HKEY As Long, ByVal sSubKey As String, ByRef hkeyResult As Long) As Long
Private Declare Function RegCloseKey Lib “advapi32.dll” _
(ByVal HKEY As Long) As Long
Private Declare Function RegSetValueExA Lib “advapi32.dll” _
(ByVal HKEY As Long, ByVal sValueName As String, _
ByVal dwReserved As Long, ByVal dwType As Long, _
ByVal sValue As String, ByVal dwSize As Long) As Long
Private Declare Function RegCreateKeyA Lib “advapi32.dll” _
(ByVal HKEY As Long, ByVal sSubKey As String, ByRef hkeyResult As Long) As Long

Public Enum HKEY_Enum
HKEY_CLASSES_ROOT = &H80000000
HKEY_CURRENT_USER = &H80000001
HKEY_LOCAL_MACHINE = &H80000002
HKEY_USERS = &H80000003
HKEY_CURRENT_CONFIG = &H80000004
HKEY_DYN_DATA = &H80000005
End Enum

Public Function SetRegValue(ByVal HKEY As HKEY_Enum, ByVal Path As String, _
ByVal Entry As String, ByVal Value As String) As Boolean

‘ Set text value in registry
Dim lngKey As Long
On Error GoTo Proc_Error

If RegOpenKeyA(HKEY, Path, lngKey) 0 Then
RegCreateKeyA HKEY, Path, lngKey
End If
SetRegValue = (RegSetValueExA(lngKey, Entry, 0&, 1&, Value, Len(Value) + 1) = 0)
RegCloseKey lngKey
Exit Function

Proc_Error:
MsgBox Err.Description, vbCritical
End Function

Public Sub SetPDFFileName(sPath As String)
SetRegValue HKEY_CURRENT_USER, “SoftwareAdobeAcrobat PDFWriter”, _
“PDFFileName”, sPath
End Sub

Reply | Quote

Thanks Hans. That worked. Now I have one more problem. After setting the printer, I cannot seem to save the report to the PDF file.

a) If I use Docmd.OutputTo, I can only save it in one of the Microsoft formats (eg RTF, etc..) , which corrupts the PDF file.
If I use Docmd.Printout, this doesn’t let me specify the destination.

What is the best way to print to an acrobat file, in a specific location?

Reply | Quote

Hans,

Thanks for the advice – this is exactly what I am looking for, except…..I don’t have a Printers Collection (I’ve checked the object browser and online help). Do you know which references I need to include to get this collection?

Thanks again!

Larry

Reply | Quote

The original question was for Access 2002; the Printers collection was introduced in that version. If you are using Access 2000 or earlier, the Printers collection is not available.

Reply | Quote

Thanks – do you know of an easy way to accomplish this in Access 2000? I’ve got a “Free PDF” driver which I want to use (per another post) and want to print reports to disk using it on demand.

Thanks again!

Larry

Reply | Quote

As far as I know, setting the printer in Access 2000 is very ugly – see Controlling Your Printer. This chapter from the Access Developer’s Handbook is for Access 95, but it applies to Access 97 and 2000 as well.

Perhaps someone else has a simpler solution

Reply | Quote

As far as I know, setting the printer in Access 2000 is very ugly – see Controlling Your Printer. This chapter from the Access Developer’s Handbook is for Access 95, but it applies to Access 97 and 2000 as well.

Perhaps someone else has a simpler solution

Reply | Quote

Thanks – do you know of an easy way to accomplish this in Access 2000? I’ve got a “Free PDF” driver which I want to use (per another post) and want to print reports to disk using it on demand.

Thanks again!

Larry

Reply | Quote

The original question was for Access 2002; the Printers collection was introduced in that version. If you are using Access 2000 or earlier, the Printers collection is not available.

Reply | Quote

Hans,

Thanks for the advice – this is exactly what I am looking for, except…..I don’t have a Printers Collection (I’ve checked the object browser and online help). Do you know which references I need to include to get this collection?

Thanks again!

Larry

Reply | Quote

I have tried the access PDF report code to produce the PDF reports automatically but am having no luck , could you assist?

Shall l send you the code?

Any help would be appreciated.

Justin.

Reply | Quote

Hans being away, post the code here and someone will have a look at it.

Reply | Quote

Hans being away, post the code here and someone will have a look at it.

Reply | Quote

I have tried the access PDF report code to produce the PDF reports automatically but am having no luck , could you assist?

Shall l send you the code?

Any help would be appreciated.

Justin.

Reply | Quote

Look up the Printer object and property and the Printers collection in the online help, and look up the name of the “printer” for Acrobat (I don’t have that myself, so I don’t know the name)

Examples:

Me.Printer = Application.Printers(“Acrobat”)

where Me refers to the report, if the code is in the report module, or

Reports!MyReport.Printer = Application.Printers(“Acrobat”)

where MyReport is an open report.

Reply | Quote

There is a utility floating around called “FreePDF”, which supposedly allows you to do this at no cost. You can do a Google search to find it, and you might also refine the search by looking for Aladdin Enterprises.

Reply | Quote

Mark,

Thanks – I’ve got the “CutePDF Printer” driver that works great – now I’m trying to select printers in VBA under Access 2000.

Larry

Reply | Quote

Mark,

Thanks – I’ve got the “CutePDF Printer” driver that works great – now I’m trying to select printers in VBA under Access 2000.

Larry

Reply | Quote

There is a utility floating around called “FreePDF”, which supposedly allows you to do this at no cost. You can do a Google search to find it, and you might also refine the search by looking for Aladdin Enterprises.

Reply | Quote

Francois

see attached code.

Justin

Reply | Quote

What are you using to create pdf’s ? FreePDF ? The site seems to bee off-line and I can’t get it.
In the code you use ExportPDF “……” . Is these a function you write ?

Reply | Quote

I am using Adobe PDF writer.
I got the function ExportPDF from the coding provided.

Justin.

Reply | Quote

Can you post the ExportPDF function ?

Reply | Quote

see attached code.

Justin

Reply | Quote

The attached code is the same as in the previous post.
I have been playing the last hours with programming in the registry.
So far I can change the printer to print to the pdf distiller printer but until now I can’t find how to avoid the dialog to ask the file name.
If I find something, I’ll post back

Reply | Quote

It would be really appreciated if you could sort this out for me , as it would save a lot of time.

Justin.

Reply | Quote

For the moment, if using windows xp, I can set the printer to print to pdf in code.
In Acrobat distiller you have an option to avoid to ask where to save. If you use this, the report is saved as a pdf file with the name of the report. sample: ReportName.pdf.
But this is saved on the desktop and I’m looking for a method to save it in a directory of your choice. That’s the last problem.

Reply | Quote

I have found the option in Adobe Acrobat for not prompting for filenames.
Would you be able to send me the completed code so l can try it out.

Thanks for your help.

Thanks
Justin.

Reply | Quote

I clean it up a little and send post it in a moment.

Reply | Quote

Many thanks, l assume you’ve managed to work it so that the .pdf file can go into any directory?

Reply | Quote

Many thanks, l assume you’ve managed to work it so that the .pdf file can go into any directory?

Reply | Quote

I clean it up a little and send post it in a moment.

Reply | Quote

This program is tested on a win xp and I don’t know if it will run on other windows version.
In the attachment you’ll find to txt files.
Extract them and copy the content of each of the files in a different new module. Save the modules but don’t use the name RunReportAsPDF as this is the name of the sub and a sub may not have the same name as a module.
For each report you want to run, use an instruction as follow :
Call RunReportAsPDF(“ReportName”,”PrinterName”)
Replace ReportName with the name of your report.
Replace Printer Name with the printer name you find in the registry when the Adobe Distiller is set as default.
To find this out :
Set the adobe distiller as default printer.
Go to Start , Run and type regedit.
Navigate to the key HKEY_CURRENT_USERSoftwareMicrosoftWIndows NTCurrentVersionWindows
Look in the right pane form the key Device and note the string. (On my pc it’s : Acrobat Distiller,winspool,Ne01: )
This is the text you have to use as PrinterName.
Close the regedit.
Set you usual printer as default back.

Let me know how it turns out.

Reply | Quote

I have found the printer name from the steps you mentioned below.

What l am unsure about is to how run the code , do l need a command button and use the “On Click” event.
see attachment.

Justin.

Reply | Quote

Yes, use a command button and in the click event use code like this :
Private Sub cmdPrintPDF_Click()
Call RunReportAsPDF(“Report1”, “Acrobat Distiller,winspool,Ne01:”)
Call RunReportAsPDF(“Report2”, “Acrobat Distiller,winspool,Ne01:”)
Call RunReportAsPDF(“Report3”, “Acrobat Distiller,winspool,Ne01:”)
Call RunReportAsPDF(“Report4”, “Acrobat Distiller,winspool,Ne01:”)
Call RunReportAsPDF(“Report5”, “Acrobat Distiller,winspool,Ne01:”)
End Sub

The reports will be on the desktop. I can’t find the method to put them in a specific directory for the moment.

Reply | Quote

Yes, use a command button and in the click event use code like this :
Private Sub cmdPrintPDF_Click()
Call RunReportAsPDF(“Report1”, “Acrobat Distiller,winspool,Ne01:”)
Call RunReportAsPDF(“Report2”, “Acrobat Distiller,winspool,Ne01:”)
Call RunReportAsPDF(“Report3”, “Acrobat Distiller,winspool,Ne01:”)
Call RunReportAsPDF(“Report4”, “Acrobat Distiller,winspool,Ne01:”)
Call RunReportAsPDF(“Report5”, “Acrobat Distiller,winspool,Ne01:”)
End Sub

The reports will be on the desktop. I can’t find the method to put them in a specific directory for the moment.

Reply | Quote

I have found the printer name from the steps you mentioned below.

What l am unsure about is to how run the code , do l need a command button and use the “On Click” event.
see attachment.

Justin.

Reply | Quote

Does it matter what names l call the modules?

Is module1 and module 2 ok?

Justin.

Reply | Quote

That’s ok

Reply | Quote

I have run the code but appear to get an error.

I have checked for End Sub and it appears to be there , not to sure what is causing the error.
see attachment.

Justin.

Reply | Quote

You have to remove the line
Private Sub cmdPrintPDF_click
and one of the two
End Sub

Reply | Quote

I have done this and get an error.
see attachment.

Justin.

Reply | Quote

Check the line :
Call RunReportAsPDF(“CTF……….)
I can’t see the full line as the message box cover it.
Is the name of the report correct ? Did the report exist ? Are the quotes on the right place ?

Reply | Quote

The report name is fine.
Don’t know what you mean by “Did the report exist?”
Can you tell me exactly where the quotes should be?

Justin.

Reply | Quote

The report CFT…. should exist in the database window on the tab reports.
The quotes should be on the same place as in the other lines.
Call RunReportAsPDF(“CFT…..”, “Acrobat Distiller,winspool,Ne01:”)
Around the report name and around the printer name.
Did the report name contain quotes ? if so rename it in the database window and in the code line with a name without quotes.

Reply | Quote

The quotes are fine , see attachment.

I don’t understand what you are impying when you mean did the report name contain quotes?
I also don’t know what you mean when you say “The report CFT…. should exist in the database window on the tab reports”
Can you explain these in a bit more details,maybe include a screenshot so l can see what you mean.

Justin.

Reply | Quote

Look at the database window (see attachment)
Is the CFT Open Action Report in the list of reports ? Is the spelling exact the same as in the code ? Check for double spaces. See attachment one is with single space and the other with double space.
If this don’t work, select the report in the database window and press F2, then CTRL-C, go to the code window and put the cursor on the line for that report. Erase the report name and put the cursor between the two double quotes (“|”) end press CTRL-V

Reply | Quote

The reports are there ok. They need to be different to the . PDF report name.
I don’t know what you mean by
“If this don’t work, select the report in the database window and press F2, then CTRL-C, go to the code window and put the cursor on the line for that report. Erase the report name and put the cursor between the two double quotes (“|”) end press CTRL-V”
I tried this but it did not work , see attachment.

Justin

Reply | Quote

That was to use a copy and past to copy the name of the report in the code.
In the lines calling the procedure where I told you to put the report name you should enter the exactly same name as appear in the database window, surrounded by double quotes.
From your attachment I see a report named Install CFT
If you want a pdf file from that report use the line :
Call RunReportAsPDF(“Install CFT”,”Acrobat Distiller,winspool,Ne00:”)
and when you run the code you’ll have on the desktop a file named : Install CFT.pfd

Reply | Quote

The PDF report name needs to be different from the report name which l have already created.
This l am certain is the problem l am having.
Is there a solution to this?

Justin.

Reply | Quote

I don’t understand.
You have created reports. You want them on paper or as pdf.
If you want them on paper, use the normal procedure : DoCmd OpenReport …
If you want them in a pdf file use my code.
if you need different reports on paper than in pdf, you’ll have to create new reports and use these new reports to create pdf files.

Reply | Quote

If you see the most recent post to D. Willett you will see what l mean.

Justin

Reply | Quote

If you see the most recent post to D. Willett you will see what l mean.

Justin

Reply | Quote

I don’t understand.
You have created reports. You want them on paper or as pdf.
If you want them on paper, use the normal procedure : DoCmd OpenReport …
If you want them in a pdf file use my code.
if you need different reports on paper than in pdf, you’ll have to create new reports and use these new reports to create pdf files.

Reply | Quote

Justin

I think there’s a bit of confusion here.
why does the PDF need to be a different name to the Access report.
It would be much simpler to rename the reports within Access.

You can right click on a report and select “Rename”
The reports must be named exactly as the code is trying to pull.

If your users will be viewing the PDF’s as opposed to the Access Reports, this doesn’t really seem to be an issue.

Reply | Quote

It needs to be different for requirement reasons.
see attachement for an example as to what l mean.

Justin.

Reply | Quote

Justin

1.Which version will your users view: Access or PDF ?

2. Who has access to the Access reports: Just you, or many users ?

3. Why do the names have to be different.

If you’re not specific in your answers, it’s extremely difficult to help you.

Reply | Quote

Dave,

Response to your questions:

1.Which version will your users view: Access or PDF ? PDF

2. Who has access to the Access reports: Just you, or many users ? Myself and others.

3. Why do the names have to be different. User requirements

Reply | Quote

Justin

Sorry I can’t help you further on this.

Francois, thank you for the conversion code, it is proving to be usefull within my application.
I think I have a solution to the save location, I’ll test it further.

Reply | Quote

Justin

Sorry I can’t help you further on this.

Francois, thank you for the conversion code, it is proving to be usefull within my application.
I think I have a solution to the save location, I’ll test it further.

Reply | Quote

You need to discuss the user requirements with your users because someone doesn’t understand the situation. Just because there is a user requirement doesn’t mean that it makes sense. Access and pdf reports *always* have a different name because Access reports don’t exist outside the Access database and pdf reports don’t exist inside it. The pdf report will have a pdf extension, while an Access report “printed” to Word will have an rtf extension. What more difference do you need?

Reply | Quote

I fully understand they have different extension names.
When l am impying difference names l mean a different report name to a different .pdf filename.

Justin.

Reply | Quote

If you rename your reports to what the user want, you will have your pdf files with the right names.
If your application is well designed, the users would never see a report name. You should provide a menu or switch board, with whatever description you want, to launch the reports.
If you persist, the only (very bad) solution I see is duplicating the reports and use the ones for pdf files and the others for paper prints.

Reply | Quote

If l rename the report names then this would take the same amount of time as typing out the .pdf filesname in the first
place , therfore no need for the additional coding which you have provided.
I have designed a command button to run the reports.
If there is not a solution to not having to re-type out the pdf filenames , then l will see the user to see if he will allow me to call the .pdf filenames the same name as the report names.

Thank you for your help today , it has been much appreciated.

Justin.

Reply | Quote

What are you talking about when you say “file names”? The pdf version of the report, once printed to the pdf printer, definitely has a file name. The Access report NEVER has a file name because it is not a FILE. The only way you get a file name for an Access report is to print it or output it to an rtf or excel format, for instance. Most PDF printers have a method of some sort that allows you to pass a filename to be created when generating the pdf file. You’ll need to look for that in the one you’re using if you want a different name from the default generated from the Access report’s name itself.

Reply | Quote

What are you talking about when you say “file names”? The pdf version of the report, once printed to the pdf printer, definitely has a file name. The Access report NEVER has a file name because it is not a FILE. The only way you get a file name for an Access report is to print it or output it to an rtf or excel format, for instance. Most PDF printers have a method of some sort that allows you to pass a filename to be created when generating the pdf file. You’ll need to look for that in the one you’re using if you want a different name from the default generated from the Access report’s name itself.

Reply | Quote

Do you realise that the report name (I mean Filename) is generated from the Caption property of the report.

Reply | Quote

Here is how I do it using PDF995 in Access 2003.

* PDF995 has an option to specify a default filename for all files. Mine is set to catalogue.pdf. So I create the pdf, then make a copy of the file to the desired filename.
* As written my code uses another function I have not included fnGetLinkedPath that returns the location of the backend tables. You could replace that with a hardcoded value for mypath, which is where you want the file put.

* I have also included the code needed for Sleep to work, because I call that.

* There is no error handling in this yet. I have only just cobbled this together from something slightly different.

Private Declare Sub sapiSleep Lib “kernel32” _
Alias “Sleep” _
(ByVal dwMilliseconds As Long)

Sub sSleep(lngMilliSec As Long)
If lngMilliSec > 0 Then
Call sapiSleep(lngMilliSec)
End If
End Sub

Sub sbPrintReporttoPDF(strReportName As String, strPDFfilename As String, strCriteria As String)
Dim blPDFinstalled As Boolean
Dim prt As Printer
Dim mypath As String
Dim prtDefault As Printer
mypath = fngetlinkedpath() & “”
blPDFinstalled = False
‘check if there is a printer called PDF995 – cant create pdfs without it
For Each prt In Printers
If prt.DeviceName = “PDF995” Then
blPDFinstalled = True
End If
Next prt
If blPDFinstalled = False Then
MsgBox “You have asked for a PDF to be created, but PDF995 is not installed.” & vbNewLine & “PDF995 is required for creation of PDF files”
Exit Sub
End If

If Right(strPDFfilename, 3) “pdf” Then
Next prt strPDFfilename = strPDFfilename & “.pdf”
End If

Set prtDefault = Application.Printer
‘ to remember the current printer
Set Application.Printer = Application.Printers(“PDF995”)
DoCmd.OpenReport strReportName, acNormal, , strCriteria
Call sSleep(2000)
‘ otherwise the filecopy tries to happen before the previous step is finished.
FileCopy mypath & “catalogue.pdf”, mypath & strPDFfilename

Set Application.Printer = prtDefault
‘ to set the printer back

End Sub

Reply | Quote

Here is how I do it using PDF995 in Access 2003.

* PDF995 has an option to specify a default filename for all files. Mine is set to catalogue.pdf. So I create the pdf, then make a copy of the file to the desired filename.
* As written my code uses another function I have not included fnGetLinkedPath that returns the location of the backend tables. You could replace that with a hardcoded value for mypath, which is where you want the file put.

* I have also included the code needed for Sleep to work, because I call that.

* There is no error handling in this yet. I have only just cobbled this together from something slightly different.

Private Declare Sub sapiSleep Lib “kernel32” _
Alias “Sleep” _
(ByVal dwMilliseconds As Long)

Sub sSleep(lngMilliSec As Long)
If lngMilliSec > 0 Then
Call sapiSleep(lngMilliSec)
End If
End Sub

Sub sbPrintReporttoPDF(strReportName As String, strPDFfilename As String, strCriteria As String)
Dim blPDFinstalled As Boolean
Dim prt As Printer
Dim mypath As String
Dim prtDefault As Printer
mypath = fngetlinkedpath() & “”
blPDFinstalled = False
‘check if there is a printer called PDF995 – cant create pdfs without it
For Each prt In Printers
If prt.DeviceName = “PDF995” Then
blPDFinstalled = True
End If
Next prt
If blPDFinstalled = False Then
MsgBox “You have asked for a PDF to be created, but PDF995 is not installed.” & vbNewLine & “PDF995 is required for creation of PDF files”
Exit Sub
End If

If Right(strPDFfilename, 3) “pdf” Then
Next prt strPDFfilename = strPDFfilename & “.pdf”
End If

Set prtDefault = Application.Printer
‘ to remember the current printer
Set Application.Printer = Application.Printers(“PDF995”)
DoCmd.OpenReport strReportName, acNormal, , strCriteria
Call sSleep(2000)
‘ otherwise the filecopy tries to happen before the previous step is finished.
FileCopy mypath & “catalogue.pdf”, mypath & strPDFfilename

Set Application.Printer = prtDefault
‘ to set the printer back

End Sub

Reply | Quote

Do you realise that the report name (I mean Filename) is generated from the Caption property of the report.

Reply | Quote

If l rename the report names then this would take the same amount of time as typing out the .pdf filesname in the first
place , therfore no need for the additional coding which you have provided.
I have designed a command button to run the reports.
If there is not a solution to not having to re-type out the pdf filenames , then l will see the user to see if he will allow me to call the .pdf filenames the same name as the report names.

Thank you for your help today , it has been much appreciated.

Justin.

Reply | Quote

If you rename your reports to what the user want, you will have your pdf files with the right names.
If your application is well designed, the users would never see a report name. You should provide a menu or switch board, with whatever description you want, to launch the reports.
If you persist, the only (very bad) solution I see is duplicating the reports and use the ones for pdf files and the others for paper prints.

Reply | Quote

I fully understand they have different extension names.
When l am impying difference names l mean a different report name to a different .pdf filename.

Justin.

Reply | Quote

You need to discuss the user requirements with your users because someone doesn’t understand the situation. Just because there is a user requirement doesn’t mean that it makes sense. Access and pdf reports *always* have a different name because Access reports don’t exist outside the Access database and pdf reports don’t exist inside it. The pdf report will have a pdf extension, while an Access report “printed” to Word will have an rtf extension. What more difference do you need?

Reply | Quote

Dave,

Response to your questions:

1.Which version will your users view: Access or PDF ? PDF

2. Who has access to the Access reports: Just you, or many users ? Myself and others.

3. Why do the names have to be different. User requirements

Reply | Quote

Justin

1.Which version will your users view: Access or PDF ?

2. Who has access to the Access reports: Just you, or many users ?

3. Why do the names have to be different.

If you’re not specific in your answers, it’s extremely difficult to help you.

Reply | Quote

It needs to be different for requirement reasons.
see attachement for an example as to what l mean.

Justin.

Reply | Quote

Justin

I think there’s a bit of confusion here.
why does the PDF need to be a different name to the Access report.
It would be much simpler to rename the reports within Access.

You can right click on a report and select “Rename”
The reports must be named exactly as the code is trying to pull.

If your users will be viewing the PDF’s as opposed to the Access Reports, this doesn’t really seem to be an issue.

Reply | Quote

Users won’t be able to just “view” pdf reports the way they do an Access report. The PDF report is created by printing the Access report to a PDF printer. You will NEVER see the pdf report in the database window because it isn’t in Access, it’s a pdf file on the drive and it has to be opened with a pdf reader.

Reply | Quote

Users won’t be able to just “view” pdf reports the way they do an Access report. The PDF report is created by printing the Access report to a PDF printer. You will NEVER see the pdf report in the database window because it isn’t in Access, it’s a pdf file on the drive and it has to be opened with a pdf reader.

Reply | Quote

The PDF report name needs to be different from the report name which l have already created.
This l am certain is the problem l am having.
Is there a solution to this?

Justin.

Reply | Quote

That was to use a copy and past to copy the name of the report in the code.
In the lines calling the procedure where I told you to put the report name you should enter the exactly same name as appear in the database window, surrounded by double quotes.
From your attachment I see a report named Install CFT
If you want a pdf file from that report use the line :
Call RunReportAsPDF(“Install CFT”,”Acrobat Distiller,winspool,Ne00:”)
and when you run the code you’ll have on the desktop a file named : Install CFT.pfd

Reply | Quote

The reports are there ok. They need to be different to the . PDF report name.
I don’t know what you mean by
“If this don’t work, select the report in the database window and press F2, then CTRL-C, go to the code window and put the cursor on the line for that report. Erase the report name and put the cursor between the two double quotes (“|”) end press CTRL-V”
I tried this but it did not work , see attachment.

Justin

Reply | Quote

The report name is different to the .PDF name.
The reports are there.

Reply | Quote

The report name is different to the .PDF name.
The reports are there.

Reply | Quote

Look at the database window (see attachment)
Is the CFT Open Action Report in the list of reports ? Is the spelling exact the same as in the code ? Check for double spaces. See attachment one is with single space and the other with double space.
If this don’t work, select the report in the database window and press F2, then CTRL-C, go to the code window and put the cursor on the line for that report. Erase the report name and put the cursor between the two double quotes (“|”) end press CTRL-V

Reply | Quote

Justin

Francois has given all the information you require to complete your task.
What you must do is to create the reports first in your database.
The code relies on the reports first being present.
If the reports are not present, then the code has nothing to convert, unless as Francois suggests, they are not named correctly.

Reply | Quote

The reports are present , altough there name needs to be different to the .PDF name.
Does this make a diffrence?

Justin

Reply | Quote

The reports are present , altough there name needs to be different to the .PDF name.
Does this make a diffrence?

Justin

Reply | Quote

Justin

Francois has given all the information you require to complete your task.
What you must do is to create the reports first in your database.
The code relies on the reports first being present.
If the reports are not present, then the code has nothing to convert, unless as Francois suggests, they are not named correctly.

Reply | Quote

The quotes are fine , see attachment.

I don’t understand what you are impying when you mean did the report name contain quotes?
I also don’t know what you mean when you say “The report CFT…. should exist in the database window on the tab reports”
Can you explain these in a bit more details,maybe include a screenshot so l can see what you mean.

Justin.

Reply | Quote

The report CFT…. should exist in the database window on the tab reports.
The quotes should be on the same place as in the other lines.
Call RunReportAsPDF(“CFT…..”, “Acrobat Distiller,winspool,Ne01:”)
Around the report name and around the printer name.
Did the report name contain quotes ? if so rename it in the database window and in the code line with a name without quotes.

Reply | Quote

The report name is fine.
Don’t know what you mean by “Did the report exist?”
Can you tell me exactly where the quotes should be?

Justin.

Reply | Quote

Check the line :
Call RunReportAsPDF(“CTF……….)
I can’t see the full line as the message box cover it.
Is the name of the report correct ? Did the report exist ? Are the quotes on the right place ?

Reply | Quote

I have done this and get an error.
see attachment.

Justin.

Reply | Quote

You have to remove the line
Private Sub cmdPrintPDF_click
and one of the two
End Sub

Reply | Quote

I have run the code but appear to get an error.

I have checked for End Sub and it appears to be there , not to sure what is causing the error.
see attachment.

Justin.

Reply | Quote

That’s ok

Reply | Quote

Does it matter what names l call the modules?

Is module1 and module 2 ok?

Justin.

Reply | Quote

This program is tested on a win xp and I don’t know if it will run on other windows version.
In the attachment you’ll find to txt files.
Extract them and copy the content of each of the files in a different new module. Save the modules but don’t use the name RunReportAsPDF as this is the name of the sub and a sub may not have the same name as a module.
For each report you want to run, use an instruction as follow :
Call RunReportAsPDF(“ReportName”,”PrinterName”)
Replace ReportName with the name of your report.
Replace Printer Name with the printer name you find in the registry when the Adobe Distiller is set as default.
To find this out :
Set the adobe distiller as default printer.
Go to Start , Run and type regedit.
Navigate to the key HKEY_CURRENT_USERSoftwareMicrosoftWIndows NTCurrentVersionWindows
Look in the right pane form the key Device and note the string. (On my pc it’s : Acrobat Distiller,winspool,Ne01: )
This is the text you have to use as PrinterName.
Close the regedit.
Set you usual printer as default back.

Let me know how it turns out.

Reply | Quote

I have found the option in Adobe Acrobat for not prompting for filenames.
Would you be able to send me the completed code so l can try it out.

Thanks for your help.

Thanks
Justin.

Reply | Quote

For the moment, if using windows xp, I can set the printer to print to pdf in code.
In Acrobat distiller you have an option to avoid to ask where to save. If you use this, the report is saved as a pdf file with the name of the report. sample: ReportName.pdf.
But this is saved on the desktop and I’m looking for a method to save it in a directory of your choice. That’s the last problem.

Reply | Quote

It would be really appreciated if you could sort this out for me , as it would save a lot of time.

Justin.

Reply | Quote

The attached code is the same as in the previous post.
I have been playing the last hours with programming in the registry.
So far I can change the printer to print to the pdf distiller printer but until now I can’t find how to avoid the dialog to ask the file name.
If I find something, I’ll post back

Reply | Quote

see attached code.

Justin

Reply | Quote

Can you post the ExportPDF function ?

Reply | Quote

I am using Adobe PDF writer.
I got the function ExportPDF from the coding provided.

Justin.

Reply | Quote

What are you using to create pdf’s ? FreePDF ? The site seems to bee off-line and I can’t get it.
In the code you use ExportPDF “……” . Is these a function you write ?

Reply | Quote

Francois

see attached code.

Justin

Reply | Quote

If you have the full version of Adobe Acrobat Try this. It uses the PDF writer of Acrobat
You have to have PDF writer installed. Check your printers folder for this.
If not re-install Acrobat, do a custom install and select PDF writer option.

Reply | Quote

Is there a way to make this work “WITHOUT” prompting the user for the file name?

Reply | Quote

Assume you have found the answer. As a help to others reading this post, to suppress Save as dialog, open pdf995.ini and add the line Output File=SAMEASDOCUMENT.

Reply | Quote

If you have the full version of Adobe Acrobat Try this. It uses the PDF writer of Acrobat
You have to have PDF writer installed. Check your printers folder for this.
If not re-install Acrobat, do a custom install and select PDF writer option.

Reply | Quote