CODEWITHSUNDEEP
excelvba
user3877643
user3877643

Reputation: 31

Run VBA code on specific time

I found the following code on this web site "save Excel file as CSV" is there any way with help of you'll I can auto run this code on specific time (11:00 pm every night), and save file name as current date (hr2015-05-05), any help will be greatly appreciated. Thanks

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFileName As String

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With

Set Sourcewb = ActiveWorkbook
TempFileName = Sourcewb.FullName + ".csv"

'Copy the sheet to a new workbook
ActiveSheet.Copy
Set Destwb = ActiveWorkbook

'Save the new workbook and close it
With Destwb
    .SaveAs Filename:=TempFileName, FileFormat:=xlCSV, ConflictResolution:=xlLocalSessionChanges
    .Close SaveChanges:=False
End With

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With
End Sub

Edit fixed tag issue

Upvotes: 1

Views: 10004

Answers (1)

Sam
Sam

Reputation: 948

I would do this one of two ways.

One would be through windows task scheduler. Where you will set up a task to open Excel and run it that way. See http://www.mrexcel.com/forum/excel-questions/302970-task-scheduler-vbulletin-script-auto-open-excel.html

Or

Use the code

Application.OnTime TimeValue("23:00:00"), SUBNAMEHERE

Changing SUBNAMEHERE to the sub where the code is. Not sure if it will run the beforesave part through

EDIT added code to have filename

Change TempFileName = Sourcewb.FullName + ".csv" to be

TempFileName = Sourcewb.FullName & " hr" & format(now,"YYYY-MM-DD") & ".csv"

Upvotes: 7

Related Questions