ontime function

[elfranzen]

OK think I have too much code since it seems to be running about 8 times instead of the once.

This is a sample of what I have in the thisworkbook code

Application.OnTime TimeValue("03:00:00"), "Macro03"
     Application.OnTime TimeValue("03:03:00"), "Macro04count"
     Application.OnTime TimeValue("03:15:00"), "Macro04"
     Application.OnTime TimeValue("03:30:00"), "Macro04"
     Application.OnTime TimeValue("03:45:00"), "Macro04"
     Application.OnTime TimeValue("04:00:00"), "Macro04"
     
     Application.OnTime TimeValue("04:03:00"), "Macro05count"
     Application.OnTime TimeValue("04:15:00"), "Macro05"
     Application.OnTime TimeValue("04:30:00"), "Macro05"
     Application.OnTime TimeValue("04:45:00"), "Macro05"
     Application.OnTime TimeValue("05:00:00"), "Macro05"

and here is what I have in one of the macros I call (names of the servers have been changed)

Application.Visible = False
    Application.OnTime TimeValue("03:15:00"), "Macro04"
    Application.OnTime TimeValue("03:30:00"), "Macro04"
    Application.OnTime TimeValue("03:45:00"), "Macro04"
    Application.OnTime TimeValue("04:00:00"), "Macro04"
    Sheets("04").Select
    With Selection.QueryTable
        .Connection = "URL;http://myserver"
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.PublishObjects.Add(xlSourceChart, _
     "S:\mywebpage1.htm" _
     , "Peak", "", xlHtmlStatic, "DevXSample_16365", _
     "my webpage 1").Publish (True)
     
     ActiveWorkbook.PublishObjects.Add(xlSourceChart, _
     "S:\mywebpage2.htm" _
     , "Combined", "", xlHtmlStatic, "DevXSample_16365", _
     "my webpage 2").Publish (True)
     
     
    ActiveWorkbook.PublishObjects.Add(xlSourceChart, _
     "S:\mywebpage3.htm" _
     , "Combined", "", xlHtmlStatic, "DevXSample_16365", _
     "my webpage 3").Publish (True)
     Application.Visible = True

do I need all of this? I have 48 macros that run at different hours.

Thanks for all the help

 

[DCrake]

I think you need to set a case statement up to examine the time and if relevant do the necessary

Select case Time()
   Case x,y,z
     Do this
   Case Else
     Do Nothing
End Select

 

[elfranzen]

Ok you lost me. can you explain what each line of the code does so I can understand what is happening here. Thanks

 

[DCrake]

I am assuming that you want to do something at certain points of the day, right?

And as to cut down your syntax we can approach this by using a case statement

So first we get the current time and store it to a variable

NowTime = Time()

Next we look at what time it is and if it a time we are interested in we proceed to do something

Select Case NowTime
    Case "03:03:00"
       Call Macro4Count
    Case ""03:15:00", "03:30:00", "03:45:00", "04:00:00"
      Call Macro4
    Case "04:03:00"
       Call Macro5Count
    Case ""04:15:00", "04:30:00", "04:45:00", "05:00:00"
      Call Macro5
    Case etc
End Select

If NowTime does'nt match any of the cases it does nothing

David

 

[elfranzen]

So this would go in the workbook part of the code?

what about the macro part of the code would I need all the ONTIME functions in there?

 

[elfranzen]

OK I have put DIM NowTime as String at the top of the workbook code
and added NowTime = Time() (won't this only happen once do I need to call this funtion over and over again

and added

Select Case NowTime
Case "10:15:00", "10:36:00", "10:45:00", "11:00:00"
Call Macro11
Case etc
End Select

just this in the work book to see if it worked and it didn't what I am I doing wrong. I have taken out all the Time functions out for this hour in the workbook code and the macro code.

Thanks

 

[elfranzen]

I have this code running and when 24 hours is up will it start over because it didn't seem like it did. just thought I would ask if anyone knows.

 

[DCrake]

You have to remember that when you open the form the on timer event fires exactly in x number of milliseconds. so if you set it to 60,000 which is one minute if you open the form at 09:09:09 at 09:10:09 it will fire again.

You problem is that you are asking for checks to see if the time is 09:15:00 the emphasis being on the seconds. Therefore you case statement will never be met. In order to get round this problem you will need to remove the seconds element of the equasion and just test for minutes past the hour, not minutes and seconds.

David

 

[elfranzen]

would this also fix the 24 hr limit I have run into.