Code to export my query as XLS to same directory each week.

[cw via AccessMonster.com]

I have the following query: qryEquipment_Intelebill
- which I output as an Excel file
- save to a shared folder
- email the link to a co-worker
- first Monday of every week

How do I make this process completely automatic?..

- On Monday morning have the database open,
- run the query & save as Excel 2003~2007
- to my shared folder & always overwrite the old file
- then forward a LINK to that file only, in the Body of the email to the same
co-worker

I have worked with parts of this kind of procedure before but a little
unclear on putting it all together in a single unattended process.

Currently, I use a command button with this code behind it, then I email the
file while in Excel:
----------------------------------------------
Private Sub Command17_Click()
On Error GoTo Err_Handler
DoCmd.OutputTo acOutputQuery, "qryEquipment_Intelebill", acFormatXLS, "",
True
Exit_Sub:
Exit Sub
Err_Handler:
If Err = 2501 Then
Else
MsgBox "Error " & Err.Number & " " & Err.Description
End If
Resume Exit_Sub
End Sub

 

[CTomac]

I have the following query: qryEquipment_Intelebill
- which I output as an Excel file
- save to a shared folder
- email the link to a co-worker
- first Monday of every week

How do I make this process completely automatic?..

- On Monday morning have the database open,
- run the query & save as Excel 2003~2007
- to my shared folder & always overwrite the old file
- then forward a LINK to that file only, in the Body of the email to the same
co-worker

I have worked with parts of this kind of procedure before but a little
unclear on putting it all together in a single unattended process.

Currently, I use a command button with this code behind it, then I email the
file while in Excel:
----------------------------------------------
Private Sub Command17_Click()
On Error GoTo Err_Handler
DoCmd.OutputTo acOutputQuery, "qryEquipment_Intelebill", acFormatXLS, "",
True
Exit_Sub:
   Exit Sub
Err_Handler:
   If Err = 2501 Then
   Else
       MsgBox "Error " & Err.Number & "  " & Err.Description
   End If
   Resume Exit_Sub
End Sub

To auto-send the email, add this after you output to excel:

DoCmd.SendObject acSendNoObject, , , "[Your Recipient]", , ,
"[Subject]" , _
"[Link here], 0

 

[jalexander via AccessMonster.com]

Thanks for the help.
Question: How can I alter my code to always save the file to the same place?

I have the following query: qryEquipment_Intelebill
- which I output as an Excel file

[quoted text clipped - 35 lines]To auto-send the email, add this after you output to excel:

DoCmd.SendObject acSendNoObject, , , "[Your Recipient]", , ,
"[Subject]" , _
"[Link here], 0

 

[CTomac]

Thanks for the help.
Question: How can I alter my code to always save the file to the same place?

I have the following query: qryEquipment_Intelebill
- which I output as an Excel file

[quoted text clipped - 35 lines]To auto-send the email, add this after you output to excel:

DoCmd.SendObject acSendNoObject, , , "[Your Recipient]", , ,
"[Subject]" , _
        "[Link here], 0

DoCmd.OutputTo acOutputQuery, "qryEquipment_Intelebill", acFormatXLS,
"\\server\path\file.xls", True

**Use True (last argument here) if you want to open Excel and view the
file, False if you don't.

 

[cw via AccessMonster.com]

Thankyou!
I will try this again and then lastly see if I can get the code to run on a
schedule, every Monday morning at 8:00am.
cw

Thanks for the help.
Question: How can I alter my code to always save the file to the same place?

[quoted text clipped - 12 lines]
DoCmd.OutputTo acOutputQuery, "qryEquipment_Intelebill", acFormatXLS,
"\\server\path\file.xls", True

**Use True (last argument here) if you want to open Excel and view the
file, False if you don't.