Hey everybody....first of all, even though this is my first post here, I have been using these forums as a tremendous resource to help develop some pretty awesome macros that have saved myself and my company an incredible amount of time, so thank you to those who contribute here.
On to my problem...I have a macro that I need to execute each night at 1 AM. This macro is opening up a spreadsheet, filtering data within this spreadsheet to new spreadsheets, and then emailing the new spreadsheets to the appropriate contacts. I created the below vbscript to execute this macro:
As you can see above, I've added in some lines to write output to a text file so that I can track the progress of the the script. Whenever I launch the script by double clicking on it, everything executes perfectly. Whenever I try to run the script as a scheduled task, the log file doesn't make it past "3" so it appears to be getting stuck on
Does anybody have any experience with this? As far as the scheduled task goes, the user account set to run the task is an admin and the task is set to run w/ elevated rights. I've tried calling the script using several different methods, but none work. Any advice would be appreciated.
On to my problem...I have a macro that I need to execute each night at 1 AM. This macro is opening up a spreadsheet, filtering data within this spreadsheet to new spreadsheets, and then emailing the new spreadsheets to the appropriate contacts. I created the below vbscript to execute this macro:
Code:
Dim objExcel
Set objFileToWrite = CreateObject("Scripting.FileSystemObject").OpenTextFile("C:\logfile.txt",2,true)
Set objExcel = CreateObject("Excel.Application")
objFileToWrite.WriteLine("1")
objExcel.Application.DisplayAlerts = False
objFileToWrite.WriteLine("2")
objExcel.Visible = False
objFileToWrite.WriteLine("3")
objExcel.Workbooks.Open "PATH OF WORKBOOK CONTAINING MACRO"
objFileToWrite.WriteLine("4")
objExcel.Run "Daily"
objFileToWrite.WriteLine("5")
objFileToWrite.Close
Set objFileToWrite = Nothing
objExcel.ActiveWorkbook.Close(0)
objExcel.QuitAs you can see above, I've added in some lines to write output to a text file so that I can track the progress of the the script. Whenever I launch the script by double clicking on it, everything executes perfectly. Whenever I try to run the script as a scheduled task, the log file doesn't make it past "3" so it appears to be getting stuck on
Code:
objExcel.Workbooks.Open "PATH OF WORKBOOK CONTAINING MACRO"Does anybody have any experience with this? As far as the scheduled task goes, the user account set to run the task is an admin and the task is set to run w/ elevated rights. I've tried calling the script using several different methods, but none work. Any advice would be appreciated.
