Exporting Report to PDF specific location on quit - over-writing existing

[augcorv@gmail.com]

Hello All, Im a newb to Access.
Im attempting to export a report on database quit. Ive accomplished the export to a specific location with no problem.

The issue i am having is that when I quit the database and it exports, it prompts me, "Do you want to replace the existing file?" I want to bypass that and allow the file to be overwritten on every quit with no prompt.

What can i include in my Macro command to bypass the notification?

My current macro goes as follows:
ExportWithFormatting
Object Type Report
Object Name NameofReport
Output Format: PDF Format
Output File: C:\Users\etc\etc\filename.pdf
Auto Start: No
Template File
Encoding


Output Quality Print
QuitAccess
Options Save All

Any suggestions or guidance would be appreciated.
Thank you.

[June7]

I don't use macros, only VBA. I am not seeing an argument for ExportWithFormatting action that will automatically overwrite file.

Some events have a Response argument that can be used to automate response to popups and continue code execution. Example:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
'Suppress default Delete Confirm dialog box.
Response = acDataErrContinue
'cancel the automatic delete operation
Cancel = True
MsgBox "Must click Remove Test button to delete test from sample." , , "Delete"
End Sub

Unfortunately, that probably won't apply to your situation.

In VBA I would first check if the file already exists and if found, delete it, then do the export.

[augcorv@gmail.com]

I'm still having a problem with this function. I've tried creating a module to delete the file first, then proceed with the Macro (exporting report to PDF) but still having problems. Any suggestions or guidance would be appreciated.

My DeleteFile Module is:
'Add a reference to Scripting Runtime

Dim fso As New FileSystemObject

If fso.FileExists("H:\testingexport\testexport.pdf") Then
If MsgBox("That file exist, delete?", vbYesNo) = vbYes Then
fso.DeleteFile ("H:\testingexport\testexport.pdf")
End If
Else
MsgBox "Do not exist"
End If

set fso = nothing

[ItsMe]

You will have to describe the problem. Have you tested the function to delete the file? Perhaps you can start there by placing the code behind the click event of a button on a new blank form. Test the function by itself.

[augcorv@gmail.com]

If this Macro to Module approach is not suggested, how would i code a function to:
Within FORM:
On "BUTTON" selection:
Check to see if file.pdf exists, and if so, delete file.pdf - THEN

Export "Report" as PDF to "NetworkMappedDrive".
What im trying to achieve is whenever the database is updated, a report of all entries will be exported (PDF format) to a network drive. Boss wants to see reports pdf format.

[ItsMe]

All that I am suggesting is to test your VBA using a button and a form.


You can add VBA to the click event by selecting the Control Button while in Design View. Go to the property sheet and click the "Event Tab". There will be a list of events available. Select the ellipses(...) next the On Click field and then select "Code Builder" from the options. A new Click Event Handler in the VBA editor will appear. Your code will go between the first and last lines. Be sure to not include the first and last lines of your function.

Here is a picture of the ellipses button in the property sheet. You want to select the Event Tab and then the appropriate Event.

[augcorv@gmail.com]

thanks "ItsMe", when i do what you suggest, i recieve a compile error: User-defined type not defined. And it Highlights:
Dim fso As New FileSystemObject.

Code is as follows:


Private Sub Command203_Click()


'Add a reference to Scripting Runtime


Dim fso As New FileSystemObject


If fso.FileExists("H:\testingexport\testexport.pdf") Then
If MsgBox("That file exist, delete?", vbYesNo) = vbYes Then
fso.DeleteFile ("H:\testingexport\testexport.pdf")
End If
Else
MsgBox "Do not exist"
End If


Set fso = Nothing


End Sub

[ItsMe]

Sounds like a missing reference. You will need to add a reference. In your VBA editor, go to Tools>References. You will see a References window. Scroll down to Microsoft Scripting Runtime and select it. Click OK.

[augcorv@gmail.com]

Ok, got that - Now im getting a Compile error: Invalid outside procedure - Highlighting the filepath.

Option Compare Database


''''
' Start Code '
''''


'Add a reference to Scripting Runtime


Dim fso As New FileSystemObject


If fso.FileExists("H:\testingexport\testexport.pdf") Then
If MsgBox("That file exist, delete?", vbYesNo) = vbYes Then
fso.DeleteFile ("H:\testingexport\testexport.pdf")
End If
Else
MsgBox "Do not exist"
End If


Set fso = Nothing

[ItsMe]

Do you mean it is highlighting
If fso.FileExists("H:\testingexport\testexport.pdf") Then
????

You have two lines with file paths so it will help if you can detail exactly which line is causing the exception/error.

[augcorv@gmail.com]

Yes, Its highlighting,
If fso.FileExists("H:\testingexport\testexport.pdf") Then

[ItsMe]

You need to place the code inside of an event handler. Take a look at post #6 again. Here is a sample DB.

[augcorv@gmail.com]

This works, but im still getting a popup asking "That file exist, delete?".

Possibly something wrong in this code?
If MsgBox("That file exist, delete?", vbYesNo) = vbYes Then

[ItsMe]

That is what the code does. It is prompting you to make a decision on whether or not to delete the file. If you want to bypass the message box, simply replace

If MsgBox("That file exist, delete?", vbYesNo) = vbYes Then
fso.DeleteFile ("H:\testingexport\testexport.pdf")
End If

with

fso.DeleteFile ("H:\testingexport\testexport.pdf")

After you sort out how you want your VBA to work, you can place the code back into your function and then work on the issue of getting hte macro to call the function. Or you could bring everything into VBA. Let us know...