My VB script will not run my excel macro correctly

Question

Can you help me with a Task scheduler-Visual Basic - excel macro issue?

I created a script to open a MS Excel spreadsheet at a fixed time (2:05 pm MT) each day and run a macro that copies a field representing the current daily closing stock value and paste it into the same worksheet at another location allowing me to keep a daily history of my stock picks.

The macro works correctly when I execute it from the worksheet by running the macro or using the shortcut cntl-s.

I created a command that Task Scheduler will initiate daily at 2:05 pm. Using .bat and .vbs scripts to open the MS Excel file and run the macro that will copy a field with the latest stock values for a set of stocks that I am tracking and then paste those values into the next set of vacant cells that I designate in the spreadsheet.

The result is intended to be a daily running record of end of day stock values. ( I use 2:05 pm because I am in the mountain time zone.)

The problem I am observing is, that when executed by the task scheduler, the data copied into the updated field is the same data as the previous days data and not the current value. This happens only when executed automatically from the Task Scheduler. When I execute that macro from the spreadsheet it records the data correctly.

Following are the details. Unfortunately it appears that I cannot attach the referred to excel file. I would be happy to email it to anyone interested in helping to resolve my issue.

I appreciate any help you can provide in debugging what I have done so that it runs properly. Currently I am updating it manually by running the macro shortcut Cntl -s.

The action in Task Scheduler is "

Image

Following are the scripts that I have created:

Image

"C:\Users\kenmo\Desktop\stockrecorder.vbs"

Image

Using Visual Basic Studio:

Set xlxsApp=CreateObject("Excel.Application")

xlxsApp.Visible=true  

Set xlxsworkbook=xlxsApp.Workbooks.Open ("D:\My Personal Stock Picks with macro1.xlsm")

xlxsApp.Run(StockRecorderDaily)  

MS Microsoft Macro:

Sub StockRecorderDaily()
'
' StockRecorderDaily Macro
'
' Keyboard Shortcut: Ctrl+s
'

Range("H3:H7").Select
Selection.Copy
Range("B20").End(xlToRight).Offset(0, 1).Select
ActiveSheet.Paste
Range("A1").Select
End Sub

Thank you,

Ken kenmohnkern@Stuff .com

Answer 1

Maybe the Clipboard (Copy, Paste functions) cannot be used in your scenario? To check this, try a new worksheet and a simpler operation like copying just one cell (such as: Range("A1").Value = "Test", Range("A1").Select, Selection.Copy, Range("A2").Select, ActiveSheet.Paste). If this does not work as a task, then Clipboard does not seem available. As an alternative, you can copy the values directly, such as Range("A2").Value = Range("A1").Value. Maybe consider a loop, or give more details about the ranges to be copied.

Answer 2

Viorel-1
Thank you for your suggestion. I did as you suggested and it worked as expected.
To be clear. The macro I created works correctly when I execute it from the worksheet using 'Run' macro function or the shortcut I created. It only fails to copy the correct data when it is executed via the VB script from Task Scheduler. In this case it appears to run the macro, as it ends up in the correct data range, but the data that is placed in the range is the same as the previous dates data. and not what should have been copied from the current dates source range. This is what I don't understand. I am willing to make the excel file available to anyone who would test/debug it on their computer.
Ken

Answer 3

When scheduling the job, be sure to specify that it only runs when user is logged in, using the current user's user account.
Unattended execution is iffy. Office products are not designed for that.

Answer 4

TvanStiphout,

Thanks. It is scheduled to run only when user is logged in. It's run on my personal desktop. I'm always logged in as the user and no one else has access to it.
The scheduler runs the macro the results are just not what the macro does when executed from the excel worksheet.

Ken