I have a macro that runs on Open in and Excel file. I tried to schedule it to open itself every day at a specified time with Task Scheduler. I have Windows XP and it doesn't appear to be opening the file just saving a new copy which still requires me to click Yes on save which completely defeats the purpose of automation! Any ideas? I've seen this done with Windows 7... (IMG:style_emoticons/default/confused.gif)

Sounds like you are missing a line or parameter on a line in your automation code. Can you post the last few lines that closes the excel file when its finished doing whatever you are doing with it please?

Yes, it definitely appears there is an issue and most likely with the excel/vba code, not the task scheduler.

Any chance the excel file is a template file type? And if you could post all of the code you're using it would help.

This post has been edited by ipisors: May 30 2012, 02:53 PM

I need to be able to open and use this file periodically throughout the day which is why I didn't add code to close the file. I'm open to suggestions though. Here's my code, ipisors this should look familiar to you (IMG:style_emoticons/default/cool.gif)

Private Sub Workbook_Open()
Call GetMostRecentFileStatusRev1
ActiveWorkbook.Save

End Sub

Option Explicit

Sub GetMostRecentFileStatusRev1()

Dim FileSys As FileSystemObject
Dim wbSrc As Workbook
Dim objFile As File
Dim myFolder
Dim strFilename As String
Dim dteFile As Date


'set path for files - change for your folder
Const myDir As String = "\\Tulfs1\shared\Download\D981\Data Dump"

Application.DisplayAlerts = False

'set up filesys objects
Set FileSys = New FileSystemObject
Set myFolder = FileSys.GetFolder(myDir)


'loop through each file and get date last modified. If largest date then store Filename
dteFile = DateSerial(1900, 1, 1)
For Each objFile In myFolder.Files
If objFile.DateLastModified > dteFile Then
dteFile = objFile.DateLastModified
strFilename = objFile.Path
End If
Next objFile

Set wbSrc = Workbooks.Open(strFilename)
wbSrc.ActiveSheet.Range("A2:AD150000").Copy Workbooks("burndown.xlsm").Sheets("Database Module").Range("A3")
Application.CutCopyMode = False
wbSrc.Close False

Workbooks("burndown.xlsm").Sheets("2015").Calculate

Application.Goto Workbooks("burndown.xlsm").Sheets("2015").Range("A2")
Application.DisplayAlerts = True


End Sub

I think that this should be this

If i'm correct the false bit is the SaveChanges parameter.


Edit: Forgot to change it to True - Sorry about that

This post has been edited by JonSmith: May 30 2012, 03:43 PM

What should be changed?

I feel like there are a few uncertainties here. You're saying several things in your original post.

1) it doesn't appear to be opning the source file. Well, could it be happening in a split second? vba is pretty fast, unless you happen to know that the opening and saving of this particular file should take long enough to see..

2) jon is right, in that, wbSrc.Close False means that you're closing wbSrc without saving it. However, I'm not sure if that's what you want to do or not.

3) Lastly, no matter what the issue is or isn't.....YOu definitely should not have ActiveWorkbook.Save in the beginning of your code. You simply cannot know whether vba will consider the ActiveWorkbook to be the one you want it to be, at any given time.

If you want it to save the workbook where you're typing the vba code, then use ThisWorkbook.Save

If you want it to be the workbook defined as wbSrc, then take Jon's advice....change wbSrc.Close False to wbSrc.Close True

And finally, if you're getting a Save prompt, that's an entirely different issue.

This post has been edited by ipisors: May 30 2012, 03:16 PM

Maybe I'm understanding the Task Scheduler wrong... You're right ipisors I dont' want the copied file saved. I also tried doing a simple test macro in that all it does is add 3 lines of text and when the task scheduler run its prompts me to Save once again but even then it never ran the code on the Open event...Tell me if I'm wrong but I don't think it's my code that's causing the Save. Or maybe there is some additional code you have to write to make these two things work together (Task Scheduler and Excel).

Sorry, I've edited my post so it has the correct code.

I think you need to set a breakpoint in the code (on the 2nd line of code) on the workbook where you have this code. Then have the task scheduler open that workbook. When the workbook opens, press F8 in order to find out specifically which line of code is prompting the Save dialogue.

I really don't think I'll be able to tell myself. there are too many things I'm just not sure of, like other conditions in your source workbook that I'm not sure of (which could possibly trigger that).

You don't have any code in the Workbook_BeforeClose event of that workbook, do you?

If you don't, i can't think of any reason why:

wbSrc.Close (False) shouldn't work well, to avoid the SAve prompt. Especially since you have application.displayalerts=false (turned off) anyway! Definitely we need to find out specifically at which point in the code this save prompt is happening.

Try changing wbSrc.Close False to wbSrc.Close (False)

I included a file I use for this purpose. It isn't as flexible as the solution to which I am working, but it does work.

First I use the following script in the task scheduler. It is a bat file that runs Executive.xlsm and passes the parameter ON to it. There is code in Executive.xlsm to read passed parameters.

"C:\Program Files\Microsoft Office\Office12\EXCEL.EXE" "C:\Users\dflak\Local\0000 IVC and POS Analysis\Executive.xlsm" /e/ON

If you open Executive.xlsm, the control panel page in cell B2 should read “Running in non-compute mode.” If you try to execute the macro RunSpreadsheets, it will short-circuit and quit. This is what allows me to do maintenance on the spreadsheet. If you manually type in ON in cell B2 it will run the macro.

If you look at the parameters page you will see how I set up things. Columns A&B are holidays, Columns E&F actually run things and Cell I1 is used to tell Excel where to find these spreadsheets.

I set this up very specifically so I could run the spreadsheets in a batch instead of setting up each with its own task. Daily runs every day. Weekly runs on the first workday of the week.

To be truthful, the trigger function in the task scheduler is much more flexible. My suggestion is use the task scheduler to determine when to run and on the parameters page, list the files you want to run and call them Daily (which means they can be run on any day when this spreadsheet is called).

Then in each of the listed files I have a macro called Executive. The code in Executive.xlsm calls each listed spreadsheet and looks for a macro called Executive to run. The Executive macro in each spreadsheet calls the other macros in the spreadsheet.

I can still run the spreadsheets manually.