Run Macro in Excel twice a day? 1

[AccessHelp123]

Hi,

I am trying to run a macro in Excel(automatically twice a day) that connects to a MySQL database and retrieves data.

The file will be located on a Sharepoint server. Is there a way to code in VBA so that the file automatically retrieves data twice a day saves the file and sends an e mail once the update is done. Any help is appreciated.

Thanks.

 

[SkipVought]

I do just as you stated 3 times a day.

You must have the Workbook open.

Use Application.OnTime to schedule running the next procedure.

My example

Sub Schedule1()
    Select Case Time
        Case Is > [GraveyardStart]
            Application.OnTime [MorningStart], "Schedule2"
            [ScheduleNbr] = 1
        Case Is < [MorningStart]
            Application.OnTime [MorningStart], "Schedule2"
            [ScheduleNbr] = 1
        Case Is < [AfternoonStart]
            Application.OnTime [AfternoonStart], "Schedule2"
            [ScheduleNbr] = 2
        Case Is < [GraveyardStart]
            Application.OnTime [GraveyardStart], "Schedule2"
            [ScheduleNbr] = 3
    End Select
    
    [ScheduledRun] = True
    
End Sub

Sub Schedule2()
    
    Main       'the procedure that runs
    
    Schedule1  'after if finishes, schedule next
    
End Sub

In Main I have the following code to eMail a notification...

Public Function CdoSend( _

    MailTo As String, _

    MailFrom As String, _

    Subject As String, _

    MessageText As String, _

    Optional CC As String, _

    Optional BCC As String, _

    Optional FileAttachment As String) As Boolean

On Error GoTo CdoSend_Err

 

' This example use late binding, you don't have to set a reference

' You must be online when you run the sub

    Dim oMsg As Object

    Dim oConf As Object

    Dim Flds As Variant

 

    Set oMsg = CreateObject("CDO.Message")

    Set oConf = CreateObject("CDO.Configuration")

 

        oConf.Load -1    ' CDO Source Defaults

        Set Flds = oConf.Fields

        With Flds

            .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2

            .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = "dfwmail.bh.textron.com"

            .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25

            .Update

        End With

 

    With oMsg

        Set .Configuration = oConf

        

        .To = MailTo

        .CC = CC

        .BCC = BCC

        .FROM = MailFrom

        

        .Subject = Subject

        .TextBody = MessageText

 

        

        If Len(FileAttachment & "") > 0 Then

            

            '## Last make sure the file actually exists and send it!:

            Dim fso

            Set fso = CreateObject("Scripting.FileSystemObject")

            If fso.FileExists(FileAttachment) Then

                .AddAttachment FileAttachment

            Else

                'otherwise return that the send failed and exit function:

                Debug.Print "[CdoSend.Error]=> File attachment path does not exist, quitting..."

                CdoSend = False

                Exit Function

            End If

        

        End If

    

        '## Send zee message! ##

        .sEnd

    

    End With

 

    Set fso = Nothing

    Set oMsg = Nothing

    Set oConf = Nothing

    

    CdoSend = True

 

CdoSend_Exit:

    Exit Function

    

CdoSend_Err:

    Debug.Print "[CdoSend.Error(" & Err.Number & ")]=> " & Err.Description

    CdoSend = False

    Resume CdoSend_Exit

End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AccessHelp123]

I will try this out. Thanks.

 

[Zink71]

i find the ontime function flakey at best.

I use a scheduler program, which runs Excel, then I send keystrokes to a menu add-in i create that then runs the code. But, you need 3rd party scheduler to accomplish this...

 

[SkipVought]

i find the ontime function flakey at best.

How so.

I've used this technique sucessfully for years to run Excel applications. I currently run an application at 5:00, 13:00 and 21:00. This only probelems I encounter have nothing to do with the scheduling of the run.

Runs like, well, er, uhhhhhh...

CLOCKWORK!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]