Optimisation of Excel VBA script

Question

I already have a script in an Excel sheet working. I have mentioned it below:

Sub Auto_Open()
'
' Auto_Open Macro
'
ActiveWorkbook.RefreshAll
'
End Sub


Private Sub Workbook_Open()
   Worksheets("Sheet2").Range("A4:BX33").Copy
   Worksheets("November").Range("C3").PasteSpecial Transpose:=True

   Worksheets("Sheet2").Range("A34:BX64").Copy
   Worksheets("December").Range("C3").PasteSpecial Transpose:=True

   Worksheets("Sheet2").Range("A65:BX95").Copy
   Worksheets("January").Range("C3").PasteSpecial Transpose:=True

   Worksheets("Sheet2").Range("A96:BX123").Copy
   Worksheets("February").Range("C3").PasteSpecial Transpose:=True

   Worksheets("Sheet2").Range("A124:BX154").Copy
   Worksheets("March").Range("C3").PasteSpecial Transpose:=True

   Worksheets("Sheet3").Range("A2:BX100").Copy
   Worksheets("Weekly").Range("C3").PasteSpecial Transpose:=True

   Worksheets("Sheet4").Range("A2:DZ100").Copy
   Worksheets("Monthly Figures").Range("C2").PasteSpecial Transpose:=True

   Worksheets("Sheet5").Range("A2:BX100").Copy
   Worksheets("All Time Figures").Range("C1").PasteSpecial Transpose:=True

   Application.CutCopyMode = False 
End Sub

Thanks to the great people on SO this code works great. Now the additional functionalities I want to add are:

a) Open this sheet automatically at 6 A.M. everyday. b) Save it without any prompts in Excel. c) Close the sheet.

I searched online and came up with these solutions:

Private Sub Workbook_Open()
    Application.OnTime TimeValue("15:00:00"), "mymacro"
End Sub

Workbooks.Open Filename:="C:\test.xls"

Workbooks("Test.xls").Close

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub

Kindly advise on how to integrate the above given ideas into my existing script and how to optimise the script.

One issue I am faceing with the current script is that before the refresh of data is over, the transpose happens. Is there anyway to solve this too.

Thank you in advance for your help.

WIth regards, Manus

I think you could achieve this through task scheduler http://www.7tutorials.com/advanced-users-task-creation-task-scheduler — Sathish Kothandam, Apr 03 '14 at 14:03
You definitely want to add some tags to this. Maybe your operating system and task scheduler. — Stepan1010, Apr 03 '14 at 19:08
See http://stackoverflow.com/questions/22771185/how-to-set-recurring-schedule-for-xlsm-file-using-windows-task-scheduler/22773677#22773677 — brettdj, Apr 04 '14 at 04:13

score 1 · Answer 1 · edited Apr 05 '14 at 15:42

You'll want to do a few things:

Move your code from Workbook_Open to a module file
Write a VBScript file that opens the workbook, executes the sub-routine, then closes Excel after saving
Create a task that executes the VBScript file

Here's the code, put this in a new module in your workbook

Option Explicit

Public Sub DoWork()

    ThisWorkbook.RefreshAll

    Worksheets("Sheet2").Range("A4:BX33").Copy
    Worksheets("November").Range("C3").PasteSpecial Transpose:=True

    Worksheets("Sheet2").Range("A34:BX64").Copy
    Worksheets("December").Range("C3").PasteSpecial Transpose:=True

    Worksheets("Sheet2").Range("A65:BX95").Copy
    Worksheets("January").Range("C3").PasteSpecial Transpose:=True

    Worksheets("Sheet2").Range("A96:BX123").Copy
    Worksheets("February").Range("C3").PasteSpecial Transpose:=True

    Worksheets("Sheet2").Range("A124:BX154").Copy
    Worksheets("March").Range("C3").PasteSpecial Transpose:=True

    Worksheets("Sheet3").Range("A2:BX100").Copy
    Worksheets("Weekly").Range("C3").PasteSpecial Transpose:=True

    Worksheets("Sheet4").Range("A2:DZ100").Copy
    Worksheets("Monthly Figures").Range("C2").PasteSpecial Transpose:=True

    Worksheets("Sheet5").Range("A2:BX100").Copy
    Worksheets("All Time Figures").Range("C1").PasteSpecial Transpose:=True

    Application.CutCopyMode = False

End Sub

Then you want to create a VBScript file that will open your workbook (Task Scheduler has issues with just opening the file itself, things can go wonky).

Dim objExcel

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open("path to your workbook here")

objExcel.Visible = False
objExcel.DisplayAlerts = False

objExcel.Run "DoWork"

objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close(0)
objExcel.Quit

Save that somewhere, doesn't matter where, just remember it. Now you want to create a task that will execute that VBScript file.

Open up Task Scheduler (Control Panel -> Administrative Tools -> Task Scheduler)
Click "Create Basic Task..." in the right hand panel
Give it a name and description. Click Next.
Tell it to Trigger "Daily". Click Next. Give it a start date and what time you want it to run every day. Tell it to recur every day. Click Next.
Select "Start a program". Click Next.
In the program/script box, put in the path of your VBScript file.

You're good to go, this task will now run every day at the time you specified, starting on the day you specified. The task will execute the script which will create a new instance of Excel in the background (not interfering with anything you're doing), run your updates, save and close.

score 0 · Answer 2 · answered Apr 01 '14 at 13:29

Piece of cake, just call the first sub from the second sub. You can solve the transpose before refresh by breaking the sub into two seperate subs. I'd also suggest you turn off updates at the beginning of the sub and turn them back on at the end. Like so:

Private Sub updatetoggleoff()

    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

End Sub
Private Sub updatetoggleon()

    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Private Sub Workbook_Open()

updatetoggleoff

Application.OnTime TimeValue("15:00:00"), "mymacro"
End Sub

Workbooks.Open Filename:="C:\test.xls"

Auto_Open

updatetoggleon

Workbooks("Test.xls").Close

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub

which means you have to have the workbook opened at all times. I think the OP wants to use something like Task Scheduler to open the file. I may be wrong though — , Apr 02 '14 at 14:43

Ken · Answer 3 · 2014-04-03T21:16:33.163

Create a VBS script that is triggered by task scheduler.

Use Notepad and paste this into the a file called TEST.VBS

Set objExcel = CreateObject("Excel.Application") 
Set objWorkbook = objExcel.Workbooks.Open("C:\test.xlsx") 
objworkbook.Application.DisplayAlerts = False
objExcel.Calculate
objexcel.Save
objexcel.quit

Create a task scheduler event to run TEST.VBS every morning at 6am.

Program: wscript.exe

Arguments: c:\test.vbs //B

Optimisation of Excel VBA script

3 Answers3