run macro from closed workbook automatically for every 24 hours

[punna111]

Hi all,

I have a file which is having name as "Team data-11/23/2012"
actually i have to create a file on daily basis except Saturday and Sunday.
I need a macro which automatically runs from one file and saves the excel file on daily basis. and the file name should "team data - (today's date)". this file also should be created automatically. it means that macro should run automatically for every 24 hours.

any help highly appreciated...


Thanks & Regards,
PRB.

 

[kpark91]

Hello,
a macro can be created to just adjust the file name you want to work on and
you can use a scheduler to run the macro every 24 hours.

Since you never mentioned how the file should be created or what should be on it, I doubt I can write a macro for it lol.

 

[punna111]

Thank you very much for replying this thread.

please confirm me how can i do this?

i have one file named "team data-date" file. i will use scheduler for open that file at a particular time. and this file should has the macro to run at every 24 hours.
That macro should create the same file with same sheets but name of the file should be different. all these work should done background only.

PRB.

 

[taurean]

See if this thread is of any help to you:
Solved: Initiate .xlsm file with task scheduler - VBA Express Forum

 

[tlowry]

This may be what you want.

Putting these two subs in a module will:

• Create a copy of the master file upon opening of the master file
• Start a timer to make a copy at every time interval
• This will continue until the Excel file is closed

You will need to do the following:

• Open a new Excel file (Don’t use a “real” file until you are comfortable with this code)
• Put this code into it’s own module
• Save the file as MasterTimer.xls for this testing
• Close the file
• Create a folder called “C:\Timer\”
• Make the macro security level low Tools | Macro | Security
• Open MasterTimer.xls

Look at “C:\Timer\” in the file explorer and see copies being made every minute.

Option Explicit
 
Const SavePathconst = "c:\timer\"
Const sinterval = "00:01:00"  '"23:59:59"
Const DateFmt = "yy mm dd hh mm ss"
Const prefac = "team data - "
Const masterfile = "MasterTimer.xls"
 
 
Sub auto_open()
    If ThisWorkbook.Name <> masterfile Then Exit Sub
    DoSaveFile
End Sub
Sub DoSaveFile()
    Dim savepath As String
    Select Case Weekday(Now())
        Case vbSunday            'vbSaturday, vbSunday
        Case Else
            On Local Error Resume Next
            savepath = Replace(SavePathconst & "\", "\\", "\")
            MkDir savepath
            ThisWorkbook.SaveCopyAs savepath & prefac & _
                            Format(Now(), DateFmt) & ".xls"
    End Select
    Application.OnTime Now() + TimeValue(sinterval), "DoSaveFile"
End Sub

You will need to change some things to fit your situation. I suggest that you do this one at a time.

Const SavePathconst = "c:\timer\"
Const sinterval = "00:01:00" '"23:59:59"
Const DateFmt = "yy mm dd hh mm ss"
Const prefac = "team data - "
Const masterfile = "MasterTimer.xls"

 

[punna111]

Thank you for this macro.

I have tried this macro. it is working fine, but issue is when i open master file and run the macro it is saving a file per minute.

To open a file i can use scheduler. when this file opened by scheduler that macro should run automatically and save the file. then that file should close automatically.

This is what i need.

Regards.
PRB

 

[tlowry]

If you change the sinterval as below, it will save every 24 hours (less a second)

Const sinterval = "23:59:59"

The reason I did not do that is I didn't have the time to wait

You will also want to change;

Case vbSunday            'vbSaturday, vbSunday

to

Case vbSaturday, vbSunday

so it won't make copies on Saturday or Sunday (again I didn't want to wait until Monday)

 

[punna111]

please confirm me 3 things...

1>after opening of this master file with scheduler this macro will run automatically or not?
2>after running macro and saving a file in folder, master file will close or not?
3>all these work is running background or not?

 

[tlowry]

1>after opening of this master file with scheduler this macro will run automatically or not?

Yes, it will run automatically

2>after running macro and saving a file in folder, master file will close or not?

It will not close, but it can be modified to do so.

3>all these work is running background or not?

Not this, but the scheduler will

here is what I think you want:

An Excel file that when run by the scheduler, will

• open a master file (read only)
• save a copy of the master file to another Excel file (with naming convention and location as stated)
• close the read only instance of the master file (without changes)
• close itself

 

[punna111]

What i want is.........
* With scheduler master file will be opened.
* From the Master file Macro should run automatically when file opened by scheduler.
* Then with macro Master file create another file in desired directory, with desired name.(Team data- Date)
* After that Master file should close automatically.
* This should be done on daily basis.

Thatsall.....

Please confirm me one thing-- can i run a macro from closed workbook at a certain time.

Thanks,
PRB.