+ Reply to Thread
Results 1 to 3 of 3

Time based macro running multiple times

Hybrid View

  1. #1
    Registered User
    Join Date
    10-25-2013
    Location
    sc
    MS-Off Ver
    Excel 2007
    Posts
    32

    Time based macro running multiple times

    Hello Forum,

    I'm having the following problem with a macro I've written to run at a certain time of day (Note: Due to work restrictions I cannot use windows scheduler to run the macro at the time required), the code runs several times and thus generates multiple emails. I'm wondering if there is a simple way to solve this so that it only runs once. Code is below:

    
    Dim TimeToRun
    
    Sub auto_open()
        Call ScheduleCopyPriceOver
    End Sub
    
    Sub ScheduleCopyPriceOver()
    
        TimeToRun = Now + TimeValue("00:00:01")
        Application.OnTime TimeToRun, "ScheduleCopyPriceOver"
    
        If Time = TimeSerial(7, 30, 0) Then
            Application.OnTime TimeToRun, "CopyPriceOver"
        End If
    
    End Sub
    
    Sub CopyPriceOver()
        
    Dim txtfilename As String
    Dim currentWb As Workbook
    Dim openWb As Workbook
    Dim newWb As Workbook
    Dim openWs As Worksheet
    Dim currentWs As Worksheet
    Dim OutApp As Object
    Dim OutMail As Object
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    
    
    txtfilename = "file location"
    
    Set currentWb = ThisWorkbook
    
    Set openWb = Workbooks.Open(txtfilename)
        
    openWb.Sheets("Tabelle1").Activate
    
    ActiveSheet.Columns("A:F").Select
    
    Selection.Copy
    
    currentWb.Sheets("Tabelle1").Activate
    
    ActiveSheet.Cells(1, 1).Activate
    
    ActiveSheet.Paste
    
    Calculate
    
    currentWb.Save
    
    openWb.Close
        
    Set OutApp = CreateObject("Outlook.Application")
    
    Set OutMail = OutApp.CreateItem(0)
    
    With OutMail
        
        .To = "boss"
        .CC = "me"
        .BCC = ""
        .Subject = "Update"
        .attachments.Add currentWb.FullName
        .send
        
    End With
    
    Set OutMail = Nothing
    Set OutApp = Nothing
        
    Call ScheduleCopyPriceOver
    
    End Sub
    
    Sub auto_close()
    
        On Error Resume Next
    
        Application.OnTime TimeToRun, "ScheduleCopyPriceOver", , False
    
        ActiveWorkbook.Close
    
    End Sub
    Thanks in advance!

  2. #2
    Registered User
    Join Date
    10-23-2014
    Location
    Raleigh, NC
    MS-Off Ver
    2010
    Posts
    6

    Re: Time based macro running multiple times

    Seems like you could simply use a flag, set it to TRUE once the process has been run, then check it to see if it has already been run that day before it tries again. Like this:
    dim bFlag as boolean
    .
    .
    bflag = false
    .
    .
        If Time = TimeSerial(7, 30, 0) Then
            
            do while not bflag
                 Application.OnTime TimeToRun, "CopyPriceOver"
                bflag = true
                exit do
           loop
    
       End If
    .
    .
    .
    Last edited by bimbim9; 10-27-2014 at 02:48 PM.

  3. #3
    Registered User
    Join Date
    10-25-2013
    Location
    sc
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Time based macro running multiple times

    I assume I would set bflag = false in the schedulecopypriceover sub? Wouldn't bflag just reset to false again when it runs back through the schedulecopypriceover sub?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Running the same command multiple times
    By mej in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-11-2013, 12:03 AM
  2. Macro for running the SQL code multiple times
    By sawoodalam1989 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-27-2013, 08:35 PM
  3. Macro for running the SQL multiple times
    By sawoodalam1989 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-27-2013, 07:00 AM
  4. Macro to Sum Multiple Times in Column Based on Variable Range
    By kareni in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-22-2011, 08:15 PM
  5. help fixing my macro from running multiple times
    By MurseBry in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-19-2010, 02:17 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1