Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- : : |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open Excel file from VB and open MACRO | Excel Discussion (Misc queries) | |||
open up an excel program with an auto open macro | Excel Programming | |||
Macro - Open Word with Excel macro | Excel Discussion (Misc queries) | |||
ASP: Open Excel File with Macro, Allow Macro to run, and then save | Excel Programming | |||
How do I get my personal macro worksheet to open whenever I open . | Excel Discussion (Misc queries) |