Initiate .xlsm file with task scheduler

[Rlb53]

I have generated an .xlsm file with auto open feature to intiate a series of visual basic commands.

The command sequence calls for internet access . Download and store data from a specific location. Process the data through equations. Configure the results in a specified format. Email the information to recipients. Then Close. This customarily occurs within a 12 second period. (including a 5 second pause built in to allow for macro canceling and file modification)

This was accomplished with assistance of many here at VBAExpress and it has performed flawlessly for 6 months. Thank You !

Since my "Trial" period is officially over I am attempting to bring the automation to the next level. I desire for this function to perform at a specific time, once a week, unattended.

I setup "Task Scheduler" in my Windows 7 based system for this.

At the designated time, the file is not activating although when I check the "Task Scheduler" it is indicating that the program is "Running". Hmmm.....??

My "Task Scheduler" Settings are as follows:

General:

Run whether user is logged on or not

Configure for : Windows 7 , Windows Server 2008 R2

Triggers:

Weekly: At 8:00 PM every Monday of everyweek. Enabled

Actions:

Start a program "C:\Users\Me\Desktop\Calculator.xlsm"

Conditions:

Wake the computer to run this task

Start only if the following network connection is available - Any connection

Settings:

Allow task to be run on demand

Run task as soon as possible after a scheduled start is missed

Stop the task if it runs longer than: 1 hour

If the running task does not end when requested, force it to stop
--------------------
Would anyone have a suggestion of where to check from here?

Thanks !

[Bob Phillips]

What exactly do you mena by not activating?

How are the macros initiated?

Does you code throw up any dialogs?

[snb]

Do you use a webquery ?

[Rlb53]

What exactly do you mena by not activating?

The file does not "open" as it does when the icon is Double-Clicked, initiating the auto-open macro. It is as if the file is open in the background, invisible, but it is not performing the coded tasks.





How are the macros initiated?

The initiating Macro is below.


Sub auto_open()
Application.Visible = True
Sheets("sheet4").Select
Application.DisplayFullScreen = True
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHeadings = False
Application.DisplayFormulaBar = False
Application.OnTime Now + TimeValue("00:00:05"), "weekly_update"
End Sub

From this point the macro sequence carries it through completion and Close.


Does you code throw up any dialogs?

Error Messages ? No.

The only potential operator interaction is through a command button on the opening screen. If the command button is activated, the Macro will stop. If the command button is not activated, the Macro Sequence will continue through completion.


Do you use a Webquery?

No. I access a specific webpage with a specific format of numerical data.
The webpage is copied and pasted into a Sheet within the Workbook.
The data retrieved is used in calculations.

[snb]

Why don't you use a webquery ?

Must be a very short week (extraterrestial time I suppose ?)


Application.OnTime Now + TimeValue("00:00:05"), "weekly_update"


The VBA method ontime has nothing to do with task scheduler.

[Kenneth Hobs]

Why don't you use the Open event for the workbook? I usually reference a BAT file in the task manager.

e.g.

Private Sub Workbook_Open()
Dim rc As Long, s As String, wb As Workbook, i As Integer
On Error Resume Next
'Give user an option to set manually. Otherwise, do the updates and close this workbook.
rc = MsgBoxWait("Update Now?", "Press Yes to update the Asphalt Design " & _
"files now." & vbCrLf & "Click No to abort and do manual updates." & vbCrLf & _
vbCrLf & "If no response, the updates will be made " & vbCrLf & _
"and this workbook will be closed.", 32 + 4, 5)
Select Case rc
Case 6 'Yes
GoTo EndNow
Case 7 'No
Case Else
GoTo EndNow
End Select
' Do stuff
Exit Sub
EndNow:
For Each wb In Application.Workbooks
wb.Save
Next wb
Application.Quit
End Sub

Function MsgBoxWait(strTitle As String, strText As String, _
nType As Long, nSecondsToWait As Integer)
Dim ws As Object, rc As Long
Set ws = CreateObject("WScript.Shell")
rc = ws.Popup(strText, nSecondsToWait, strTitle, nType)
Set ws = Nothing
MsgBoxWait = rc
End Function

[shrivallabha]

You can use a VBScript to run the specific macro.

1. Open a Notepad.
2. Paste the code; edit and Save with extension .vbs.
3. Schedule it to run.

I've commented the code.


'Write Sheet's full path here
strPath = "C:\MyPath\Book1.xlsm"
'Write the macro name including module
strMacro = "Sheet1.Macro2"
'Create an Excel instance and set visibility of the instance
Set objApp = CreateObject("Excel.Application")
objApp.Visible = False
'Open workbook; Run Macro; Save Workbook with changes; Close; Quit Excel
Set wbToRun = objApp.Workbooks.Open(strPath)
objApp.Run wbToRun.Name & "!" & strMacro
wbToRun.Save
wbToRun.Close
objApp.Quit
'Leaves an onscreen message!
MsgBox "Code Running Was Successful!", vbInformation

Edit:= Auto_Open seems to be legacy item from pre-2003 era. Kenneth is right about using Workbook_Open(). Also the macro won't fire if the workbook is not at a trusted location [as a macros won't be in enabled state]

[Rlb53]

Ken, Thank you for your suggestions. I'm experiencing the following results.


Why don't you use the Open event for the workbook?

Workbook_open vs Auto_open.

"Workbook_open" seems to require user interaction to begin the macro process when the file is double-clicked.
"Auto_open" initiates the macro process immediately upon workbook activation. (which is my preference for this workbook - No user interaction unless for maintenance purposes)


I usually reference a BAT file in the task manager.

i generated a BAT file in multiple configurations with no success. Could you point out my error? :dunno

start /d C:\programfiles(x86)\microsoftoffice\office14\excel.exe
start /d C:\users\me\desktop\calculator.xlsm

I tried it with singular lines as well as combined as shown.

Although my intention for this workbook is for it to operate without user interaction, I thank you for the Script to generate the Msgbox. It is definitely a useful tool for other applications I am working with ! :beerchug:


You can use a VBScript to run the specific macro.

shrivallabha, as suggested, I also generated a .vbs file in an attempt to initialize the .xlsm file.

I "Think" that I generated the syntax appropriately, but to no avail.

To assure a "Trusted" Environment, I added the digital signature / certificate to the trusted publishers list. (which was not previously listed) :clap:

The .vbs file was generated as follows:


'Write Sheet's full path here
strPath = "C:\Users\Me\Desktop\Calculator .xlsm"

'Write the macro name including module
strMacro = "workbook_open"
'Create an Excel instance and set visibility of the instance
Set objApp = CreateObject("Excel.Application")
'objApp.Visible = False (disabled to be able to view activity)
'Open workbook; Run Macro; Save Workbook with changes; Close; Quit Excel
Set wbToRun = objApp.Workbooks.Open(strPath)
objApp.Run wbToRun.name & "!" & strMacro
''wbToRun.Save (disabled - Internal Macro Sequence provides for this function)
''wbToRun.Close (Ditto)
''objApp.Quit (Ditto)
'Leaves an onscreen message!
''MsgBox "Code Running Was Successful!", vbInformation (Not Required)

I recieve an error message:

Script: C:\Users\Me\Desktop\Calculator.vbs
Line: 10
Char: 1
Error: Cannot run the macro 'Calculator.xlsm!workbook_open'. The Macro may not be available in this workbook or all macros may be disabled.
Code: 800A03EC
Source: Microsoft Excel

I continued with some research and found that a .vbs file written as:


Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open ("C:\Users\Me\Desktop\Calculator.xlsm")
objworkbook.Runautomacros 1

(using "Sub Auto_Open()" as the Macro name)

Would activate the file when double-clicked, but once again.... Not through the Task Scheduler.:banghead:

Do you see errors in the script shown?

I began to question that the task scheduler is functioning as it should. Research indicates that this is not uncommon with Task Scheduler and there may lie the problem. :think:

I'll have our IT dept. check out my system. If I find this corrects the issue, I will share the information with all.

Thank you for your assistance ! :thumb

[Rlb53]

FYI:

Task Scheduler works with the .xlsm or .vbs file with restrictions.

1: "Run only when user is logged on" Must be Selected
2: The "Start in" Option must have the File Location listed.
ex: C:\user\me\desktop\
3: Do Not Place "Quotes" around the File Location listed for the Start In Option or the file name.


If anyone has any information to share of what may be done to allow the file to run "after Hours" when the computer is not in operation...(but sleeping)....would be appreciated.

Thanks ! :)

[Kenneth Hobs]

If you use a BAT file, just put the full path and name of the XLSM into the first line.

[shrivallabha]

:
:
shrivallabha, as suggested, I also generated a .vbs file in an attempt to initialize the .xlsm file.

I "Think" that I generated the syntax appropriately, but to no avail.

To assure a "Trusted" Environment, I added the digital signature / certificate to the trusted publishers list. (which was not previously listed) :clap:

The .vbs file was generated as follows:


'Write Sheet's full path here
strPath = "C:\Users\Me\Desktop\Calculator.xlsm"
'Write the macro name including module
strMacro = "workbook_open"
'Create an Excel instance and set visibility of the instance
Set objApp = CreateObject("Excel.Application")
'objApp.Visible = False (disabled to be able to view activity)
'Open workbook; Run Macro; Save Workbook with changes; Close; Quit Excel
Set wbToRun = objApp.Workbooks.Open(strPath)
objApp.Run wbToRun.name & "!" & strMacro
''wbToRun.Save (disabled - Internal Macro Sequence provides for this function)
''wbToRun.Close (Ditto)
''objApp.Quit (Ditto)
'Leaves an onscreen message!
''MsgBox "Code Running Was Successful!", vbInformation (Not Required)
I recieve an error message:

Script: C:\Users\Me\Desktop\Calculator.vbs
Line: 10
Char: 1
Error: Cannot run the macro 'Calculator.xlsm!workbook_open'. The Macro may not be available in this workbook or all macros may be disabled.
Code: 800A03EC
Source: Microsoft Excel

I continued with some research and found that a .vbs file written as:


Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open ("C:\Users\Me\Desktop\Calculator.xlsm")
objworkbook.Runautomacros 1
(using "Sub Auto_Open()" as the Macro name)

Would activate the file when double-clicked, but once again.... Not through the Task Scheduler.:banghead:

Do you see errors in the script shown?
:
:
:
Thank you for your assistance ! :thumb
Following works for me:


'Set this path for your workbook
strPath = "C:\Users\Me\Desktop\Calculator.xlsm"
'You did not specify Module Name which was there in my suggestion
'Sheet1.Macro2 specified Module Name!
strMacro = "ThisWorkbook.Workbook_Open"
Set objApp = CreateObject("Excel.Application")
Set wbToRun = objApp.Workbooks.Open(strPath)
objApp.Run wbToRun.Name & "!" & strMacro
Just curious, why do you want to run the application in visible mode. If you were to oversee the events as they were occurring then you can run them on your own?

[Rlb53]

Thank you shrivallabha,


Just curious, why do you want to run the application in visible mode. If you were to oversee the events as they were occurring then you can run them on your own?

Disabling the visible mode was only for the initial testing.... once confirmed that the process was performing as intended... that command would be reinstated.

It is appearing that the primary cause for the file not to open with the task scheduler was conditional information setup within the task scheduler itself. (as described in previous post) Although, Task scheduler has some settings that allow for it to "Wake" the computer and run a scheduled event..... those functions are not appearing to work and I hope to find out what may be done to correct this with the .xlsm / .vbs file.

Thanks again !!

[Kenneth Hobs]

For it to wake up, you may need to supply login information.

[Rlb53]

Login information is requested and provided, when the option is selected. The task scheduler shows that the task is running at the appropriate time, but it doesn't activate the files as it should. It will show "running" until the predesignated time out is reached. (which is set for 1 hour) with no results.

[shrivallabha]

What Kenneth has suggested and it will be good to have administrator privileges on the specific machine where you want to use this macro.

[Kenneth Hobs]

Try working up a very simple example for us to troubleshoot.

[Rlb53]

I was having that exact conversation with IT an hour ago. We're going to configure it through an administrators account and try it.

We're feeling as though this will be the answer.

Thanks... and I'll let you know.

[Rlb53]

shrivallabha,

I have the Automation that I was looking for working with the file, but as with most anything... there is always "Something Else" that it would be good for the system to perform.

I am wanting to use your .vbs format to initiate the "workbook_open" macro in lieu of the "auto_open" macro so that the user may make modifications.

I'm continuing to have difficulties in getting the syntax correct.

If you would consider looking at the .xlsm file as well as the .vbs file to see where I'm incorrect, it would be appreciated.

I have uploaded a sanitized version of the file if you choose to review.

Thank you for your assistance.

[Rlb53]

The .vbs file was generated as:

strPath = "C:\Users\me\Desktop\Fuel Surcharge Calculator VBAxpress.xlsm"
strMacro = "module1.Workbook_Open"
Set objApp = CreateObject("Excel.Application")
Set wbToRun = objApp.Workbooks.Open(strPath)
objApp.Run wbToRun.Name & "!" & strMacro

[snb]

This is the way I do these things:



GetObject("G:\OF\adressen.xls").Application.Run "adressen.xls!Blad1.tst"

[shrivallabha]

Hi Rlb,

I get some errors when I try to edit the code in the file attached e.g. when I try to see Tools --> References it prompts me with a password and then Type is not defined etc. However, I can see the whole code so you need to take care of following items:

1. Workbook_Open code is kept in wrong module. It must be in "ThisWorkbook" module.

2. All Auto_Open() code shall then go to Workbook_Open(). See here (http://www.ozgrid.com/VBA/auto-run-macros.htm).

3. There's nothing wrong in the VBScript you have posted above from syntax point of view except point 1 and 2 as I have explained above.

Interesting point to note: Workbook_Open is intended to run as soon as you open the workbook. However, it doesn't run until we press enable macro settings (for security reasons). So in order to overcome this inability we have to resort to using VBS / bat file route. The code will also run otherwise when you will open file manually and enable macros. So if you edit the trust center locations to include your desktop path then you won't even need VBS!

I hope this puts you on track.

[Rlb53]

I hope this puts you on track.

shrivallabha,

Yes ! It absolutely does.

Now I have both functions operating.

I have the file operating with the task scheduler without user interaction, as originally desired.

I also have user ability to open the file for maintenance without having to hold the Shift Key and cancel macros by selecting the .vbs file.

I thank You, Kenneth and everyone else for the assistance !

I believe that I may now mark this one Solved !!

[P23champ]

Hi Rlb53 and others kindly reading,

I seem to be having the same problem as you did calling the excel macro from a VB script run from task scheduler through the "run whether user logged on or not" option....really hope you can let me know how you managed to fixed the problem of the excel macro not running through task scheduler. Seems to be a problem with Windows7 since i had the script and excel macro working very well in Windows XP. I have battled to find the solution along with IT who have tried the admin account work around but it did not work....

I tried calling the vbs script through a BAT but again did not work.....really fustrated with this problem have tried to solve it for over a week now and might have to resort to going back to XP where it worked........cant believe that is the only solution though...



Any help would be greatly appreciated.

Thanks



shrivallabha,

I have the Automation that I was looking for working with the file, but as with most anything... there is always "Something Else" that it would be good for the system to perform.

I am wanting to use your .vbs format to initiate the "workbook_open" macro in lieu of the "auto_open" macro so that the user may make modifications.

I'm continuing to have difficulties in getting the syntax correct.

If you would consider looking at the .xlsm file as well as the .vbs file to see where I'm incorrect, it would be appreciated.

I have uploaded a sanitized version of the file if you choose to review.

Thank you for your assistance.

[Rlb53]

P23Champ,

I wish I had an answer for you regarding use with Windows 7. I share your confusion with this issue.

I ran out of time and energy with the issue and my application would allow me to leave it configured to activate when the user is logged on. (although not preferred)

If anyone else has a fix for this task scheduler issue with Windows 7... I would be interested as well.

Should another Post be opened to continue this investigation?

[P23champ]

Hi Rlb53, Thanks for the quick reply!

I have managed to solve my problem this morning by downgrading back to XP and it seems to be working back as it was prior to the upgrade. So as you kindly pointed out earlier it seems Windows 7 added a new layer of security that seems to block calling excel macros via a VBS script from task scheduler.

It was extremely time consuming and very tedious tyring to find the out anything about the error and the IT team also kept trying but we ran out of patience in the end.

I would like to find a solution as I am sure one exists, because at somepoint we will have to upgrade the OS in the future.........