Automate Save As 1

[clapper62]

I'm trying to automate the creation of a report called TimeSheet this code does create the pdf file as desired but
it requires input from the user because the Save As dialog box pops up. Is there anyway to tell the Save As dialog box the file name and location to save through code?

Dim NewPrinter
Dim prtDefault

Set prtDefault = Application.Printer
NewPrinter = "PDF Writer"
Set Application.Printer = Application.Printers(NewPrinter)

stDocName = "TimeSheet"
DoCmd.OpenReport stDocName, acViewNormal

Set Application.Printer = prtDefault

"There is no pleasure in having nothing to do; the fun is having lots to do and not doing it.
." - Andrew Jackson

 

[Andrzejek]

What application?
And what would be the File Name in SaveAs?

Have fun.

---- Andy

 

[clapper62]

MS Access
and I was kind of hoping the filename would be a variable for any name I wanted but right now it comes up TimeSheet and save to the Desktop

"There is no pleasure in having nothing to do; the fun is having lots to do and not doing it.
." - Andrew Jackson

 

[Andrzejek]

In your Access - do the macro of Saving the files As (SaveAs).
What code do you get?

Have fun.

---- Andy

 

[clapper62]

Sorry not sure what you mean. this isn't a Macro it's VBA code in the click event of a button on an access form.

"There is no pleasure in having nothing to do; the fun is having lots to do and not doing it.
." - Andrew Jackson

 

[Gammachaser]

I have had significant problems with geting the PDF Printer function to completely automate. I have found ways to code where to save the file (by changing registry entries) and I can use code to change to the PDF Printer, but so far I have not found an effective software controlled way to stop the finished file from being displayed after it is generated.

But I have a workaround that is at least tolerable.

When you set up the report, go to File, Page Setup and select the 'Page' tab. Select 'Use Specific Printer' (radio button) click the 'Printer...' button. Select Adobe PDF and click the 'Properties' button. You can set the 'Adobe PDF Output Folder' to the path where you want to save the document using the 'Browse...' button. Make sure you uncheck the 'View Adobe PDF results' checkbox (default is 'checked' or True) and also uncheck 'Ask to Replace Existing File.' Click OK to save your settings.

The report gets saved to the selected location with the report's name and .pdf extension. I use code to rename the file and move it to an archive location as needed.

If you sometimes print the report to a 'standard' (i.e. hardware) printer you will have to go through this whole setup each time you want to use the PDF Printer because the 'Use Specific Printer' option is not 'sticky' (i.e. it gets set back to 'Default Printer' if someone manually prints the report to a hard copy) and if you set the selection back to the PDF Printer all of the defaults are reset. Since I have a report that is generated both manually and automatically, I keep two copies of the report (I preface the name of one report with 'Auto' and that only gets used by the automatic process.) It is a bit inconvenient when I change the report, but it works.

If anyone has a way to programmatically access those parameters, let me know. I have looked in a couple of places and have not found a workable totally automatic method.

 

[clapper62]

Thank you Gammachaser,
It's not perfect but it is a workable solution.
I've done alot of web searching on this and I'm starting to think it's not possible to do it the way I am approaching it.

"There is no pleasure in having nothing to do; the fun is having lots to do and not doing it.
." - Andrew Jackson

 

[sxschech]

Yesterday I had found a possible solution to your issue, but when I came back to tek-tips to post, I had forgotten the name of the thread as I thought it had pdf in the title. Anyway, this code seems to be able to save the file as pdf without opening the file after it is created or need to change the default printer. The only thing is that I tried it in Access 2010, not sure if it works in earlier versions or if you need specifically adobe acrobat versus a different pdf creation program. I made a few minor changes to the code so that you can pass the destination file name rather than hard coded as in the original sample (source link provided in code). If the path is not going to be fixed, you could either add that as a separate parameter or just include the path in the destination file name and comment out the hard coded path. Here is the code:

Function PrintToPDF(SrcFile As String, DestFile As String)
 '[URL unfurl="true"]http://www.access-programmers.co.uk/forums/showthread.php?t=221234[/URL]
On Error GoTo PrintToPDF_Err
 
Dim DestPath As String
'Dim DestFile As String
Dim ShowPdf As Boolean
Dim year As String

 
DestPath = "C:\Temp\"
 
'DestFile = SrcFile & "_Archived"
ShowPdf = False
 
DoCmd.OutputTo acOutputReport, SrcFile, "PDFFormat(*.pdf)", DestPath & DestFile & ".pdf", ShowPdf, "", 0, acExportQualityPrint
 
PrintToPDF_Exit:
    Exit Function
PrintToPDF_Err:
    MsgBox Error$
    Resume PrintToPDF_Exit
End Function

 

[clapper62]

Thanks sxschech but I suspect that my Access 2003 doesn't have the ability to output in .pdf format. when I run your code I get

"the format in which you are attempting to output the current object is not available" error

I think I read somewhere in my surfing that the pdf feature didn't come in until Access 2007

I am considering just going and getting 2010 just for this feature.


"There is no pleasure in having nothing to do; the fun is having lots to do and not doing it.
." - Andrew Jackson

 

[sxschech]

Sorry that the code doesn't work in 2003, I thought that might be the case.

 

[cbenisch]

I am trying to do a simlar thing and tried you changes to the original function and I get a complile error at the function call. I think I follwed you changes correctly. Any thoughts?

Option Compare Database

Private Sub Command0_Click()
DoCmd.OpenReport "Player Info Form - Current Year", acViewPreview
PrintToPDF ("Player Info Form - Current Year","Test")
DoCmd.Close acReport, "Player Info Form - Current Year", acSaveYes
End Sub

Function PrintToPDF(SrcFile As String, DestFile As String)
'

http://www.access-programmers.co.uk/forums/showthread.php?t=221234

On Error GoTo PrintToPDF_Err

Dim DestPath As String
'Dim DestFile As String
Dim ShowPdf As Boolean
Dim year As String


DestPath = "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\"

'DestFile = SrcFile & "_Archived"
ShowPdf = False

DoCmd.OutputTo acOutputReport, SrcFile, "PDFFormat(*.pdf)", DestPath & DestFile & ".pdf", ShowPdf, "", 0, acExportQualityPrint

PrintToPDF_Exit:
Exit Function
PrintToPDF_Err:
MsgBox Error$
Resume PrintToPDF_Exit
End Function

Weh I use origial function and don't try to pass the destination file name it is fine.

 

[cbenisch]

Ultimately what I need to do is a change a report that currently prints a series of records as one printout with a page break for each record so that it creates a pdf file for each record (player) in the report. If have a suggestion on how to get all the way to the end it would very helpful.