Automatically export report as PDF (1 Viewer)

napsterxp

New member
Local time
Today, 08:43
Joined
Apr 8, 2013
Messages
8
Hi there, before I start please note I have little/no VBA experience so please be kind

Relatively speaking, I've got a pretty simple database. The presenters at our community radio station fill out a form of what song they play and this (along with the system time) is stored in a table.

I have a wildcard query to bring up all entries from a certain date and at present, I export a report of this by hand on a Saturday for everyday of the previous week - but I want to automate this?!

I can change the query to be a standard one to just bring up entries for today. I then, somehow, need to automatically export a report of this query as a PDF at midnight every night into a certain folder with the file name being today's date.

This is where my knowledge starts to fizzle out so any help would be greatly appreciated and I'll of course give you a shout out on air!

Adam
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:43
Joined
Feb 19, 2013
Messages
16,553
The command to export to a pdf is:

Code:
DoCmd.OutputTo acOutputQuery, "YourQueryName", acFormatPDF, strPathAndFile, True

Set strPathAndFile to the path to the requiredfolder, filename and .pdf extension e.g. H:\CertainFolder\PlayList_01/03/2013.pdf

Not clear on what else you are asking
 

napsterxp

New member
Local time
Today, 08:43
Joined
Apr 8, 2013
Messages
8
Great thanks :)

How do I get this to run by itself at midnight every night and dynamically change the file name?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:43
Joined
Feb 19, 2013
Messages
16,553
How do I get this to run by itself at midnight

Use the form timer event to keep checking the date- obviously Access and the form need to be open for this to run. On each timer event, compare Date() with the previous Date() and if it has changed, run the outputto command (and update the previous date to the new date).

dynamically change the file name?

Use the Date function like this

strPathAndFile= "H:\CertainFolder\PlayList_" & Date() & ".pdf"
 

napsterxp

New member
Local time
Today, 08:43
Joined
Apr 8, 2013
Messages
8
You've lost me sorry :(

I took the combination of your code and put it into a class module but nothing happened. So I put the code behind a button and clicked it - nothing. I then took the date bit out and just gave it a fixed file name and all it does it output a file called 0 to the specified directory

I'm probably doing very wrong but my lack of VBA is holding me back so apologies.

As the main database is often closed by presenters, could I make another one and link the tables, query and report to the data in the main one. Then do the fancy stuff to export the PDF when the database is opened and then schedule it to open at midnight, do the export when open then close?

Thanks for all your help - much appreciated

Adam
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:43
Joined
Feb 19, 2013
Messages
16,553
Can you post your code, without knowing what you have put, I can't tell you where you are going wrong!
 

napsterxp

New member
Local time
Today, 08:43
Joined
Apr 8, 2013
Messages
8
Sure:

Behind the button I have

Code:
Private Sub Command0_Click()
DoCmd.OutputTo acOutputReport, "TodayReport", "PDFFormat(*.pdf)", strPathAndFile = "C:\Users\Adam\Desktop\Test.pdf", True, acExportQualityScreen


End Sub

and as a module named AutoExec I have

Code:
Option Compare Database

DoCmd.OutputTo acOutputReport, "TodayReport", acFormatPDF, strPathAndFile = "C:\Users\Adam\Desktop\PlaylistFor" & Date & ".pdf", True
 

napsterxp

New member
Local time
Today, 08:43
Joined
Apr 8, 2013
Messages
8
Success!

After having a bit of a fiddle, I have another database which, when opens, automatically runs that and exports the file perfectly and then closes itself :D

However, now I have to work out how to open the database at midnight automatically. Hmmmmmm??
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:43
Joined
Feb 19, 2013
Messages
16,553
Thought you were exporting a query, not a report?

Your autoexec code looks right but the button code does not:

"PDFFormat(*.pdf)" should be acFormatPDF

With regards opening the db at midnight, that is beyond my skills, you'll need to look at windows events.

But if you leave your db open on a form you can set the form timer event as follows:

In your form put the following
Code:
Option Compare Database
Dim OldDate As Date [COLOR=seagreen]'public variable[/COLOR]
 
Private Sub Form_Load()
    
    [COLOR=seagreen]'Set timer interval in milliseconds
[/COLOR]    Me.TimerInterval = 60000 [COLOR=seagreen]'fires every minute
[/COLOR]    olddate=date [COLOR=seagreen]'set olddate to today[/COLOR]
   
End Sub
Private Sub Form_Timer()
    
    If Date <> OldDate Then DoCmd.OutputTo acOutputReport, "TodayReport", acFormatPDF, strPathAndFile = "C:\Users\Adam\Desktop\PlaylistFor" & Date & ".pdf", True
    OldDate = Date 
End Sub
 

napsterxp

New member
Local time
Today, 08:43
Joined
Apr 8, 2013
Messages
8
Ok I'll give that a go thanks. And thanks for all your help so far - much appreciated. If you're free Friday 4pm-6pm be sure to tune into radionovanewport.co.uk :)

Adam
 

Users who are viewing this thread

Top Bottom