Automate Export Report (Excel)

[donsi]

Hi everyone,

I have a DB which runs 24/7. I would like to automate certain reports to be exported(Excel) at certain time with data and time included in report name. Is there a way to achive this from access itself without Task Scheduler? I don't have admin access so I can't schedule a task to have this done. Just beginner with the VBA so don't know how to code this.

One of the reports needs to be ran at 6:30AM, and it's just too early in the day to be awake.

Thank you

 

[NauticalGent]

You would need to have a form, possibly hidden, that always remained open. In that form's timer event you would place code to run at specified times. Plenty of examples in this forum for reference.

Here is one to get you started:
https://www.access-programmers.co.uk/forums/showthread.php?t=290693&highlight=Timer+events

Good luck!

 

[donsi]

I tried few ways to achieve this but failing so far.

I created a form name FrmTimer which runs on the background (hidden). It has a textbox name TxtTimer (digital clock). Time Interval is set to 1000 and On Timer event I have following code.

Private Sub Form_Timer()
Me.TxtTimer.Requery
If Me.TxtTimer = "13:51" Then
Pending_Report
End If
End Sub

Public Sub Pending_Report()
DoCmd.OutputTo acOutputReport, "RptPendingBanks", acFormatXLS, "\\NetworkPath\PedningBanks_" & Format(Date, "ddmmyy") & ".xls", True, , , acExportQualityPrint
End Sub

This is updating the time in the form but doesn't fire the event when box value is "13:51"

 

[sneuberg]

Working with time values can be tricky as underneath it all they are really doubles that represent days. To get thing's to match try using format function to round off the times.

 

[donsi]

Solved. just had to assign the variable as date and compare it with the clock.

Private Sub Form_Timer()
Dim ReportTime As Date
Me.TxtTimer.Requery
ReportTime = Me.TxtTimer
If ReportTime = "17:19:00" Then
Pending_Report
End If

End Sub

Thank you for all the help.

 

[HiTechCoach]

FWIW:

If the report will be generated only once a day or a few times a day, I find it much better to use the Task scheduler to fire it off. Leaving Access running all the time with a timer event is problematic.