Running a Excel Macro from Windows Scheduler

  • Thread starter Thread starter haisat
  • Start date Start date
H

haisat

Hi,
I want to run a Microsoft Excel Macro using Windows Scheduler . Could
anybody help me in this?
Thanks in advance.
 
Example.

Open a new workbook , add this code to a standard module:
''''''''''''''''''''''''''''''''''''''
Sub Initialise(text As String)

MsgBox text

End Sub
''''''''''''''''''''''''''''''''''''''''
save the file as C:\MyFolder\Dummy.xls
and close it.
Open Notepad and add this:
''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit

Dim XLApp
Dim wbMain
Dim FSO
Dim sExcelPath

sExcelPath = "C:\myfolder\dummy.xls"

Main

SUB Main()

Set FSO = CreateObject("Scripting.FileSystemObject")

' first check the XL file exists
If Not FSO.FileExists(sExcelPath) Then

msgbox "Unable to Find Excel File: " &
sExcelPath

else



Set XLapp = CreateObject("Excel.Application")



Set wbMain = XLApp.Workbooks.Open(sExcelPath)


with XLApp

.Run "Initialise", "Hello World"

.DisplayAlerts = False

wbMain.Close False

.DisplayAlerts = True

Set wbMain = Nothing

End With

XLApp.Quit
Set XLApp = Nothing

End If

End Sub
''''''''''''''''''''''''''''''''''
save as selecting all file types in a well know
location...with any name with the extension .VBS eg
MyDemo.VBS

close notepad

locate the vbs ( VBSript) fiel that you just created and
double click.

Whe script uses the File System Object to confirm that
the xl workbook exists, then it instantiates excel, opens
the workbook, call the procedure, passing it the text
message, then closes the workbook without saving it and
quits excel.

Excel will open invisibly- so if you have Task Manager
open to Processes you'll see it appear the diasppear.

You now need to open Windows Scheduler and set it to
start the VBS at whatever time that you want.


Patrick Molloy
Microsoft Excel MVP
 
Hi Patrik,
Thanks a lot for your response. I tried your code and it is working.
But I am little confused how to make it work for me. I am giving My
requirement clearly...
I am trying to plot a graph by taking values from a file. For this I
wrote a Macro. Now I want this macro to be run daily at particular
time. Code of the macro is pasted below.


Sub swapmemory()
'
' swapmemory Macro
' Macro recorded 10/20/2003 by ibm user
'
' Keyboard Shortcut: Ctrl+q
'
Dim sDate As String
Dim sfile As String

sDate = Format(Date, "mmddyy")
sfile = "swapmemory_results." & sDate
ChDir "C:\new Applications\Tower-G"
Workbooks.OpenText Filename:= _
"C:\new Applications\Tower-G\results\" & sfile,
Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
Range("A1:A340").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets(sfile).Range( _
"A1:A340"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:= _
sfile
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Swap Memory Results -- GLITR"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
"time(each unit = 5min)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Kb"
End With
End Sub

Could you please tell me how to run this macro daily at particular time
using windows scheduler.
 
Back
Top