Excel - Run macro at set time 2

[Wicca]

Greetings from New Zealand,

First I apologise for being a vendor (I hit the wrong option when I signed on). Im actually a very very new programmer. Well actually Im a user that has been dumped with the programmers job and no training....so sorry for silly questions.

1. How do I start an excel macro to run at five minutes after midnight each day (the data being transferred is dynamic).

2. As the WorkBook is used by other users I have covered the possibility of them changing data by "hiding" the dynamic data sheet (DataInput) and the sheet it copies to (FLOWDATA) and copying from (FLOWDATA) to the appropriate sheet within the WorkBook.

3. The Workbook is opened and closed 3 maybe 4 times a day during the normal course of events and is usually left closed at night (5.00pm - 6.00am)

Current macro is:

Sub DataTransfer()
'
' DataTransfer Macro
' Created 16/3/2004 by Jonathan (Wicca) Stammers
'


'Selection of data for appending to active "Sheet"

Sheets("DataInput").Visible = True
Sheets("FLOWDATA").Visible = True
Sheets("DataInput").Select
Range("c1:c9").Select
Selection.Copy


'Selection of new Active "Sheet"
Sheets("FlowData").Select

'Inserts day of month in header row
ActiveCell.Value = ActiveCell.Offset(0, -1) + 1

'Paste Data to required cell
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False

'Moving to new "active cell" for future data
ActiveCell.Offset(-1, 1).Range("A1").Select

'Return to user sheet and minimise workbook


ActiveWindow.SelectedSheets.Visible = False
Sheets("DataInput").Visible = False
Application.WindowState = xlMinimized
Sheets("March").Select

if you can help me refine it, I would appreciate that too. I guess to you gals and guys that are good at this, it is very clumsy but it does what I need, maybe not efficiently but its my first try...so plese bear with me.

I dont really want to have to come into work EVERY day and run the macro by hand sometime before 6.00am.

Any help you can give would be greatly appreciated.

Many thanks, I have read many posts and I just know I will get the answer I need.

With respect
Wicca

 

[longhair]

Wicca,

just create a scheduled task poiting to your excel file and make sure that your macro includes code to auto run:
Sub Autpen()
Application.Run "yourfile.xls!Menus"
End Sub
Sub Menus()
With MenuBars("Worksheet")
.Reset
.Menus.Add ("&Macros")
With .Menus("&Macros")
.MenuItems.Add ("yourfile&1"), "yourmacro" ' so you can run manually if needed
End With
End With
Application.Run "yourfile.xls!yourmacro"
End Sub

regards,
longhair

 

[SkipVought]

Hi,

Your workbook must be OPEN to run a macro. If your workbook is open, you can use the Timer function which returns the number of seconds after midnight. So five minutes after midnight is

Do
  If Timer >= 5 * 60 AND Timer < 6 * 60 then 
    Call MyMacro
    Exit Do
  End If
  DoEvents
Loop

Naturally this macro has to ALSO be running at that time.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Wicca]

Longhair and Skip

Thank you thank you thank you

This is awesome, with both answers I think I have solved my problem.....thank you thank you thank you

With respect and admiration for your skills
Wicca