Run macro on open IF its before 9:00 AM
 

I am trying to write an Auto_Open query that will refresh a workbooks
MSQueries (activeworkbook.refreshall) but ONLY if it is opened before 9:00
AM. I do this because I have a scheduler open a number of files before I
get into the office and want the refreshed done before I get there They
take a while so for the same reason I don't want them to refresh when I open
them later in the day to work on them or when other users open them after
they get into the office.

Trying this...

Sub Auto_Open()
If Time < "09:00:00" Then
HandRefreshBigPrice
Else
End If
End Sub

Where HandRefreshBigPrice is another macro that does the refresh all
function and posts the time of refresh to a hidden column.

My problem is that Excel doesn't seem to handle time the way I am expecting.
If I run this macro before 9 AM in the morning nothing happens. Then later
in the day it refreshes. Its like its opposite or something. Do I have to
somehow incorporate the day and not just the time? Is there some easier way
to make this simple task automated?

Any help would be greatly appreciated.

c-

Run macro on open IF its before 9:00 AM
 

Hi

This worked for me ;

Private Sub Workbook_Open()

Dim k

k = Time

If Time < "09:00:00" Then Call HandRefreshBigPrice


End Sub

Best N10

"ChrisR" wrote in message
...
I am trying to write an Auto_Open query that will refresh a workbooks
MSQueries (activeworkbook.refreshall) but ONLY if it is opened before 9:00
AM. I do this because I have a scheduler open a number of files before I
get into the office and want the refreshed done before I get there They
take a while so for the same reason I don't want them to refresh when I
open
them later in the day to work on them or when other users open them after
they get into the office.

Trying this...

Sub Auto_Open()
If Time < "09:00:00" Then
HandRefreshBigPrice
Else
End If
End Sub

Where HandRefreshBigPrice is another macro that does the refresh all
function and posts the time of refresh to a hidden column.

My problem is that Excel doesn't seem to handle time the way I am
expecting.
If I run this macro before 9 AM in the morning nothing happens. Then
later
in the day it refreshes. Its like its opposite or something. Do I have
to
somehow incorporate the day and not just the time? Is there some easier
way
to make this simple task automated?

Any help would be greatly appreciated.

c-

Run macro on open IF its before 9:00 AM
 

Correction
Hi

This worked for me ;

Private Sub Workbook_Open()

Dim k

k = Time

If k < "09:00:00" Then Call HandRefreshBigPrice


End Sub

Best N10

"N10" wrote in message
...
Hi

This worked for me ;

Private Sub Workbook_Open()

Dim k

k = Time

If Time < "09:00:00" Then Call HandRefreshBigPrice


End Sub

Best N10

"ChrisR" wrote in message
...
I am trying to write an Auto_Open query that will refresh a workbooks
MSQueries (activeworkbook.refreshall) but ONLY if it is opened before
9:00
AM. I do this because I have a scheduler open a number of files before I
get into the office and want the refreshed done before I get there They
take a while so for the same reason I don't want them to refresh when I
open
them later in the day to work on them or when other users open them after
they get into the office.

Trying this...

Sub Auto_Open()
If Time < "09:00:00" Then
HandRefreshBigPrice
Else
End If
End Sub

Where HandRefreshBigPrice is another macro that does the refresh all
function and posts the time of refresh to a hidden column.

My problem is that Excel doesn't seem to handle time the way I am
expecting.
If I run this macro before 9 AM in the morning nothing happens. Then
later
in the day it refreshes. Its like its opposite or something. Do I have
to
somehow incorporate the day and not just the time? Is there some easier
way
to make this simple task automated?

Any help would be greatly appreciated.

c-

Run macro on open IF its before 9:00 AM
 

Since you are working with a date/time variable and not a string, try this
instead

If Time < #9:00:00 AM# Then
....
Paul D

"ChrisR" wrote in message
...
: I am trying to write an Auto_Open query that will refresh a workbooks
: MSQueries (activeworkbook.refreshall) but ONLY if it is opened before 9:00
: AM. I do this because I have a scheduler open a number of files before I
: get into the office and want the refreshed done before I get there They
: take a while so for the same reason I don't want them to refresh when I
open
: them later in the day to work on them or when other users open them after
: they get into the office.
:
: Trying this...
:
: Sub Auto_Open()
: If Time < "09:00:00" Then
: HandRefreshBigPrice
: Else
: End If
: End Sub
:
: Where HandRefreshBigPrice is another macro that does the refresh all
: function and posts the time of refresh to a hidden column.
:
: My problem is that Excel doesn't seem to handle time the way I am
expecting.
: If I run this macro before 9 AM in the morning nothing happens. Then
later
: in the day it refreshes. Its like its opposite or something. Do I have
to
: somehow incorporate the day and not just the time? Is there some easier
way
: to make this simple task automated?
:
: Any help would be greatly appreciated.
:
: c-
:
:

Run macro on open IF its before 9:00 AM
 

And one mo

if time < timeserial(9,0,0) then



ChrisR wrote:

I am trying to write an Auto_Open query that will refresh a workbooks
MSQueries (activeworkbook.refreshall) but ONLY if it is opened before 9:00
AM. I do this because I have a scheduler open a number of files before I
get into the office and want the refreshed done before I get there They
take a while so for the same reason I don't want them to refresh when I open
them later in the day to work on them or when other users open them after
they get into the office.

Trying this...

Sub Auto_Open()
If Time < "09:00:00" Then
HandRefreshBigPrice
Else
End If
End Sub

Where HandRefreshBigPrice is another macro that does the refresh all
function and posts the time of refresh to a hidden column.

My problem is that Excel doesn't seem to handle time the way I am expecting.
If I run this macro before 9 AM in the morning nothing happens. Then later
in the day it refreshes. Its like its opposite or something. Do I have to
somehow incorporate the day and not just the time? Is there some easier way
to make this simple task automated?

Any help would be greatly appreciated.

c-

--

Dave Peterson