Automatic Daily Export Query into excel sheet

[nambi]

I apologize for me lack of knowledge with Access I am new to it, and a work in progress.

I am almost finished my project but I am stuck with one area.

One of my Queries needs to be distributed / (emailed) to several people.

I would like to take my Query and have it automatically exported once a week into an excel sheet then have this E-mailed to a few people/

I think that once I have Access automatically export into an excel sheet overwriting the same file (throughout it's cycle) I will be able to use 3rd party programs to distribute this file, but the automatic weekly export I am stuck with. Can this be done?

Thank You

 

[apr pillai]

1. Create a Table (Temp) with a single Date Field (MailDate)
2. Create a single record with date, 7 days earlier than today.
3. Copy and Paste the following Code into a Standard Module.
   
   

   Function Xport2XL()
   Dim m_MailDate As Date
   
   m_MailDate = DLookup("MailDate", "temp")
   
   'Verify Mail Schedule
   If m_MailDate + 7 > Date Then
       Exit Function ' mail not due
   End If
   
    DoCmd.OutputTo acOutputQuery, "QueryName", "Excel97-Excel2003Workbook(*.xls)", "c:\mdbs\QueryName.xls", True, "", 0, acExportQualityPrint
       
       DoCmd.SetWarnings False
       DoCmd.RunSQL "UPDATE temp SET temp.MailDate = Date();"
       DoCmd.SetWarnings True
       
   End Function

4. Create a Macro to Run the Code, we have pasted into the Module automatically with RunCode Command and type the program name Xport2XL() in the parameter line.
5. Name the Macro as Autoexec.

The code will run automatically every seven days interval and the Query will be exported into Excel and will open it when it is created.

If you need more details on this method refer the following Link:

http://www.msaccesstips.com/2007/11/automated-email-alerts/

 

[kelsita_05]

I'm trying to accomplish something similar, except on a daily basis. I need to get my query into a pipe-delimited text file, and I already have a working TransferText function set up:
DoCmd.TransferText acExportDelim, "qrydaily_export", "qrydaily", "C:\file path\CLC_MOS_" & Format(Now(), "mmddyyyy") & ".txt", False

At first glance, I thought your function here will do what I need (changing it to m_MailDate + 1 instead of 7), but the tricky part is that I need it to export each day after everyone is done working, so I'm afraid the AutoExec macro won't do the trick. Can I specify the time it runs the function some other way - ideally 11:59 pm? I've found some suggestions on how to make this happen on this forum and others, but it seems a lot are for earlier versions of Access (I'm using 2010).

Also, if anyone could give me some suggestions on exporting this straight to an FTP each day at 11:59, it would save me messing with folder syncs and other work arounds I've been considering. I can deal with the Access side of things, but this FTP stuff is WAY over my head.

Thanks so much for any suggestions you might have!