K
KenMohnkern
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 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 past 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 and the excel file. [COLOR=rgba(42, 0, 170, 1)]Comment: It appears I am unable to include the example excel file with this as an attachment. I am happy to provide it if I'm informed of how to do it. [/COLOR]
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.
Thank you,
Ken
The action in Task Scheduler is "
Following are the scripts that I have created:
"C:\Users\kenmo\Desktop\stockrecorder.vbs"
Using Visual Basic Studio:
Set xlxsApp=CreateObject("Excel.Application")
xlxsApp.Visible=true
Set xlxsworkbook=xlxsApp.Workbooks.Open("E:\My Investments\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
Continue reading...
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 past 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 and the excel file. [COLOR=rgba(42, 0, 170, 1)]Comment: It appears I am unable to include the example excel file with this as an attachment. I am happy to provide it if I'm informed of how to do it. [/COLOR]
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.
Thank you,
Ken
The action in Task Scheduler is "
Following are the scripts that I have created:
"C:\Users\kenmo\Desktop\stockrecorder.vbs"
Using Visual Basic Studio:
Set xlxsApp=CreateObject("Excel.Application")
xlxsApp.Visible=true
Set xlxsworkbook=xlxsApp.Workbooks.Open("E:\My Investments\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
Continue reading...