Create Windows Task Scheduler entry using VBA

Create Windows Task Scheduler entry using VBA


Create Windows Task Scheduler entry using VBA

आप निम्नलिखित VBA code का प्रयोग करके Windows Task Scheduler में entry कर सकते हैं और किसी भी program / application को automate कर सकते हैं। 


Step 1Excel में "Visual Basic Editor" को open करें और एक नए module को insert करें। 
Windows Task Scheduler entry using VBA

Step 2 -  अब नीचे दिए गए code को Copy & paste करने के बाद procedure को run करें 
(Note : code में path (rpathname), date (SetDate) और time (SetTime) को अपने अनुसार change कर लें )
Windows Task Scheduler entry using VBA

Step 3 - आपको निम्न confirmation आएगा कि task सफलतापूर्वक हो गया है। 
Windows Task confirmation


Step 4 - आप Windows Task Scheduler में देख सकते हैं कि आपके username folder में कल के लिए एक task create हो गया है। 
Windows Task Scheduler

 

Option Explicit

Sub Script_MachineScheduling()
'This procedure will create entry for tomorrow with same time in Windows Task Scheduler and open the test.xlsm file.
'define variables
Dim intFileNum As Integer
Dim wsh As Object
Dim SetDate As Date
Dim RobotName As String, user As String, rpathname As String, CompleteCommand As String
Dim sFileName As String, SetTime As String, sFileNameMRobot As String

Set wsh = VBA.CreateObject("WScript.Shell")

'robot name into Windows Task Scheduler
RobotName = "Robot_" & Format(Now(), "DDMMYYYY_hhmmss")
'file that will be called using Windows Task Scheduler
rpathname = "C:\Users\eomxhar\Downloads\test.xlsm"  '<< change this path to yours

SetTime = Format(Now(), "hh:mm")            '<< change the time according to your future schedule
SetDate = Format(Now() + 1, "mm/dd/yyyy")   '<< change the date according to your future schedule

user = Environ$("Username")
    'If user name is blank or not found then use below function to get it.
    If user = "" Then
        user = Mid(WorksheetFunction.Substitute((Environ$("UserProfile")), "\", "_", 2), WorksheetFunction.Find("_", WorksheetFunction.Substitute((Environ$("UserProfile")), "\", "_", 2)) + 1, 8)
    End If

user = user & "\"

'command for creating task schedule
CompleteCommand = "SchTasks /Create /SC ONCE /TN " & user & RobotName & _
            " /TR " & """" & "\" & """" & rpathname & """" & """" & " /SD " & SetDate & " /ST " & SetTime & " /f"
CompleteCommand = CompleteCommand & vbCrLf & vbCrLf & "timeout /t 10 /nobreak"

' Write code for Windows Task Scheduler entry into bat file on disk
    sFileNameMRobot = "Windows_Task_Schedule_" & Format(Now(), "DDMMYYYY_hhmmss")
    sFileName = ThisWorkbook.Path & "\" & sFileNameMRobot & ".bat"
    
    intFileNum = FreeFile
    Open sFileName For Output As intFileNum
    Print #intFileNum, CompleteCommand
    Close intFileNum

'create entry into Windows Task Scheduler using bat file
Call Shell(ThisWorkbook.Path & "\" & sFileNameMRobot & ".bat", vbNormalFocus)

Set wsh = Nothing

End Sub

एक टिप्पणी भेजें

0 टिप्पणियाँ