Solved: task scheduler to run vbs script

[aloy78]

Dear Vbaers,

I've managed to run this macro in excel itself with no problems. As I'm not familiar with running vbs script from task scheduler, i'm kinda stuck with no clue. Any help?

Here's my excel code which is running fine.

[vba]
Sub send_range()
Application.Calculate
Application.DisplayAlerts = False
Dim ws1 As Worksheet
Set ws1 = Worksheets("Tracker")
With ws1
.ListObjects(1).Unlist
.Range("A:ZZ").EntireColumn.Hidden = False ' show all hidden column
.AutoFilterMode = False
.Range("A10:J10").AutoFilter
.Range("A10:J10").AutoFilter Field:=8, Criteria1:="<=" & Now(), _
Operator:=xlOr, Criteria2:="<=" & Now() + 30
End With
ActiveSheet.Range("10:" & Range("j100").End(xlUp).Row).Select
ActiveWorkbook.EnvelopeVisible = True
With ActiveSheet.MailEnvelope
.Introduction = "This is an automated generated file. Please update the file: [Expiry Date Tracker.xlsm] in the system."
.Item.to = aloy78@yahoo.com
.Item.Subject = "REF: Expiry List"
.Item.send
End With
ActiveWorkbook.Close False

End Sub
[/vba]

I search google on how to do a vbs script, and my only clue is this:

[vba]
Dim args, objExcel

Set args = WScipt.Arguments
Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open args(0)
objExcel.Visible = True

objExcel.Run "send_range", args(1)

objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close(0)
objExcel.Quit
[/vba]

Any idea where did I go wrong in the vbs script? I believe it is the vbs script that is not working cos I have problem running my excel code.

Many thanks ahead.

[GTO]

Greetings aloy,

I have only used vbscript a very few times, but for what it is worth...

• I do not believe you need the .Arguments.
• You had a slight mis-spell at WScript
• Very minimally tested, but with the vba code in the workbook (wb) closing the wb, an error results in the vbscript attempting to close the same (already closed) wb.

Maybe add an optional parameter for the called Sub, something like:
[vba]Sub send_range(Optional IWasCalled As Boolean = False)
Dim ws1 As Worksheet

'Code...

If Not IWasCalled Then
ActiveWorkbook.Close False
End If
End Sub[/vba]

...and for the script file, something like:
[vba]Dim objExcel, WB
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set WB = objExcel.Workbooks.Open("L:\_Weekend\TempVBAX\vbax_44956\example.xlsm")
objExcel.Run "send_range", -1
WB.Close 0
objExcel.Quit [/vba]

Hope that helps,

Mark

PS - you can use WScript.ScriptFullName to return the fullname of the script file, and strip it back to the first path seperator in order not to need to hard code the workbook's fullname.

[aloy78]

Hi Mark,

Thanks for the help. Now I can run the vbs script. However, it gave me an error message prompt:

Script: D:\Personal\alyscript.vbs
Line: 5
Char: 1
Error: Unknown runtime error
Code: 800A9C68
Source: Microsoft VBScript runtime error

What does it mean? Totally clueless.

And Excel doesn't close by itself.

[shrivallabha]

Post your VBS code as you have now. We need to see what your Line 5 is.

[aloy78]

Hi Shrivallabha,

VBS code as follows:

[vba]
Dim objExcel, WB
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set WB = objExcel.Workbooks.Open("D:\personal\expirydatetracker.xlsm")
objExcel.Run "send_range", -1
WB.Close 0
objExcel.Quit
[/vba]

Here is my attached excel file:

[GTO]

Greetings Aloy,

Sorry about that, I should have included the entire procedure. The current issue is with the code in the workbook. Try:
[vba]Option Explicit

Sub send_range(Optional IWasCalled As Boolean = False)
Dim ws1 As Worksheet

Application.Calculate
'// I do not see where you are turning Alerts back on? //
'Application.DisplayAlerts = False

Set ws1 = ThisWorkbook.Worksheets("Tracker")
With ws1
.ListObjects(1).Unlist
.Range("A:ZZ").EntireColumn.Hidden = False ' show all hidden column
.AutoFilterMode = False
.Range("A10:i10").AutoFilter
.Range("A10:i10").AutoFilter Field:=8, Criteria1:="<=" & Now(), _
Operator:=xlOr, Criteria2:="<=" & Now() + 30
End With

'// Unless you are going to run this against other workbooks/worksheets, it is //
'// better to explicitly qualify ThisWorkbook and the specific worksheet. //
ActiveSheet.Range("10:" & Range("i100").End(xlUp).Row).Select

'// I think we can narrow down how long we 'fly blind' with Alerts. //
Application.DisplayAlerts = False
ActiveWorkbook.EnvelopeVisible = True
With ActiveSheet.MailEnvelope
.Introduction = _
"This is an automated generated file. Please update the file: " & _
"[Expiry Date Tracker.xlsx] in the system."
.Item.to = "aloy78@yahoo.com"
.Item.Subject = "REF: Expiry List"
.Item.send
End With
Application.DisplayAlerts = True

'// ***YIKES! You left this in, which jams the script. //
'ActiveWorkbook.Close False

If Not IWasCalled Then
ActiveWorkbook.Close False
End If
End Sub[/vba]

Hope that helps,

Mark

[aloy78]

Dear Mark,

No worries on that. I'm very new to visual basic and most of my codes are from trial n error, so I'm not sure which syntax needs to go where.

Thank you so much. Managed to get it working now.

I've posted a working copy of the workbook for those who are interested and for general sharing

Credits goes to Mark (GTO), of cos

[snb]

or:

[VBA]with GetObject("D:\personal\expirydatetracker.xlsm")
.application.Run "send_range", -1
.Close 0
end with [/VBA]

[furbs]

Guys,

I am having a similar issue with my code. I have used this forum as a resource for a while now, but never needed to posted anything until now. I have dealt with VBA a lot, but this is my first strong venture into VBS.
I am trying to use VBS to open and run my excel VBA.
The script is working like I want it to (macro runs), but I get that error and my excel.exe does not close.
The only thing that might make my scripting different is that my macro actually does a save as each day, so there are two copies. Then the master excel doc closes the new one and I have the VBS close the master doc.
This script will open an excel doc that is in a child folder and then run the macro AutoRun. I have been trying to troubleshoot this for a while now, but with no luck. I need this to run in the background without any intervention. If it encounters an error, I still need it to stop and close.

[VBS]Option Explicit

'On Error Resume Next

Dim xlApp
Dim xlBook
Dim objShell
Dim objFSO
Dim objFile
Dim strPath
Dim strFolder
Dim objArgs

Set objShell = CreateObject("Wscript.Shell")

strPath = Wscript.ScriptFullName

Set objArgs = Wscript.arguments
Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.GetFile(strPath)
strFolder = objFSO.GetParentFolderName(objFile)
Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
Set xlBook = xlApp.Workbooks.Open(strFolder & "\Templates\7am\" & objArgs(0), 0, True)
'xlApp.visible = true
xlApp.application.Run "AutoRun"
xlBook.close(false)
Set xlBook = Nothing
xlapp.quit
xlApp.DisplayAlerts = True


Set xlApp = Nothing
[/VBS]

The only other thing of note is that it will try to print itself, but there is no printer attached at the moment.

Thanks in advance!

[shrivallabha]

I see that you have commented out

On Error Resume Next

Did you try to un-comment that and run the code?

[furbs]

Shrivallabha,
I have run it with the code un-commented. I usually just comment that out when I am trying to debug it. I still have the issue either way.

The problem appears to be intermittent. The vbs ran this morning and closed itself fine and again just now. I'm not sure the difference between today and yesterday. Could an add-in that has to load on excel run cause something like this? I noticed my excel takes a while to load due to an add-in I don't use all the time.
I guess I will just post again the next time it happens. It runs twice a day.