Automated Execution of an Excel Report

[Peter]

Hi,
I have a rather nice excel report that I would like to run each day and
automatically email the output to specified users.

I'm just wondering if anyone has experience doing this that they would
be willing to share. I assume that I would use the windows scheduler
direct and I know that I can invoke excel from a vbscript which could
of course run from the scheduler.

I'm just not sure of the best way to do this. Is there a doco out there
from microsoft, or does anyone have a shell of some code to do it.
Any help would be most appreciated.

Regards,
Peter.

 

[Dave Peterson]

You want email and excel??

Take a look at Ron de Bruin's site:
http://www.rondebruin.nl

And Dick Kusleika has some sample code (especially Outlook) at:
http://dicks-clicks.com/excel/olAutomating.htm

 

[Guest]

Here is some code that I use you will have to strip out the error handler...
This is a standard module that I can import. On open it loos for the FPA
..run file in the C:\FPA directory. If that fiel exists then it opens itself
in one way. It then loos next for the Autorun.run (Empty text file used for a
flag) file exists in the C:\FPA directory. If so then it runs the autorun
code saves itself and closes itself down. If no such files exist then it
opens itself just regular without executing any code except what is in the
always run module. This code works great because the end user does not see
any update. Place a link the the spread sheet in your scheduler and the file
will update itslef over night. When you open it you get a view that is
tailored for you the creator. Reply with any questions...

Option Explici
'***********************************************************************************************
'Jim Thomlinson
'June 3, 2004
'modFPA module
'This module changes the functionality of the spreadsheet based on whether
the user is in the
'FPA group or not
'***********************************************************************************************

'Declare Module Level Constants
Private Const m_cModule As String = "modFPA" 'Used by
ErrorHandler
Private Const m_cFPADirectory As String = "C:\FPA\" 'Directory of
FPA file
Private Const m_cFPAFile As String = "FPA.RUN" 'Name of file to
indicate FPA Group
Private Const m_cAutoRunFile As String = "AUTORUN.RUN" 'Name of file to
indicate AutoRun

'***********************************************************************************************
'Name: auto_open
'Inputs: None
'Outputs: Defines the parameters under which the spreadsheet will run.
'Side Effects: None
'Calls: None
'Description: Based on whether the procedure fines the file indicated by
m_cFPADirectory and
' m_cFPAFile, it initializes the spreadsheet to work
approprite for the user
'***********************************************************************************************
Public Sub auto_open()
On Error GoTo ErrorHandler

'Initialize Application
Application.ScreenUpdating = False

Call AlwaysExecute

'Determine if the FPA file exists on the system
If UCase(Dir(m_cFPADirectory & m_cFPAFile)) = m_cFPAFile Then
'The file exists so initialize for FPA user
Call InitializeApplicationFPA
ElseIf UCase(Dir(m_cFPADirectory & m_cAutoRunFile)) = m_cAutoRunFile Then
'The file exists so initialize for AutoRun
Call InitializeApplicationAutoRun
Else
'The file does not exist so initialize for Non FPA user
Call InitializeApplicationNonFPA
End If

Application.ScreenUpdating = True
Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "auto_open"
End Sub

'***********************************************************************************************
'Name: InitializeApplicationFPA
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description
'***********************************************************************************************
Private Sub InitializeApplicationFPA()
On Error GoTo ErrorHandler
'Add Code Here
Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "InitializeApplicationFPA"
End Sub

'***********************************************************************************************
'Name: InitializeApplicationAutoRun
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description
'***********************************************************************************************
Private Sub InitializeApplicationAutoRun()
On Error GoTo ErrorHandler
'Add Code Here
Application.ActiveWorkbook.Close SaveChanges:=True
Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "InitializeApplicationAutoRun"
End Sub

'***********************************************************************************************
'Name: InitializeApplicationNonFPA
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub InitializeApplicationNonFPA()
On Error GoTo ErrorHandler
'Add Code Here
Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "InitializeApplicationNonFPA"
End Sub

'***********************************************************************************************
'Name: AlwaysExecute
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub AlwaysExecute()
On Error GoTo ErrorHandler
'Add Code Here
Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "AlwaysExecute"
End Sub

'***********************************************************************************************
'Name: auto_close
'Inputs: None
'Outputs: Defines the parameters under which the spreadsheet will exit.
'Side Effects: None
'Calls: None
'Description: Based on whether the procedure fines the file indicated by
m_cFPADirectory and
' m_cFPAFile, it ends the spreadsheet to work approprite for
the user.
'***********************************************************************************************
Public Sub auto_close()
On Error GoTo ErrorHandler

'Initialize Application
Application.ScreenUpdating = False

'Determine if the FPA file exists on the system
If UCase(Dir(m_cFPADirectory & m_cFPAFile)) = m_cFPAFile Then
'The file exists so End for FPA user
Call EndApplicationFPA
ElseIf UCase(Dir(m_cFPADirectory & m_cAutoRunFile)) = m_cAutoRunFile Then
'The file exists so End for AutoRun
Call EndApplicationAutoRun
Else
'The file does not exist so end for Non FPA user
Call EndApplicationNonFPA
End If

Application.ScreenUpdating = True
Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "auto_close"
End Sub


'***********************************************************************************************
'Name: EndApplicationFPA
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub EndApplicationFPA()
On Error GoTo ErrorHandler

Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "EndApplicationFPA"
End Sub

'***********************************************************************************************
'Name: EndApplicationAutoRun
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub EndApplicationAutoRun()
On Error GoTo ErrorHandler

Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "EndApplicationAutoRun"
End Sub

'***********************************************************************************************
'Name: EndApplicationNonFPA
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub EndApplicationNonFPA()
On Error GoTo ErrorHandler
'Add Code Here

Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "EndApplicationNonFPA"
End Sub