ThisWorkbook.Save Issue

[svetovich]

Hi all,

So this is something I've never seen before. I have a number of macros in a workbook that automate the refreshing of ODBC connections and Pivot Tables. There are multiple ThisWorkbook.Save events throughout to track and save the process of the macros. This works great when I run it manually. The issue comes in when I try to schedule it. I have a .vbs file that opens the workbook and runs the automation macro which is automated via a scheduled task. If the scheduled task is set to only run when a user is logged in then it runs perfectly. However, I need this to run when the machine is logged off. When I flag the scheduled task to run whether a user is logged on or not, the process fails. I put in some error trapping in the .vbs file and what I found is that the workbook is erring out on the first ThisWorkbook.Save. The error I'm getting is ...

Error: 1004 : Microsoft Excel cannot access the file 'D:\KAVart\Cart\Base Files\ETM CE Task PIT.xlsm'. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.

It's almost as if the workbook is being open as ReadOnly and trying to save a copy. Any help here would be greatly appreciated. The .vbs code I'm using is below ...

[FONT=courier new]Option Explicit[/FONT][FONT=courier new]
[/FONT]
[FONT=courier new]Dim xl : Set xl = CreateObject("Excel.Application")[/FONT]
[FONT=courier new]Dim wb : Set wb = Nothing[/FONT]
[FONT=courier new]Dim rp : rp     = "D:\KAVart\Cart\Base Files\ETM CE Task PIT.xlsm"[/FONT]
[FONT=courier new]
[/FONT]
[FONT=courier new]With xl[/FONT]
[FONT=courier new]    On Error Resume Next[/FONT]
[FONT=courier new]    .Workbooks.Open rp, 0, False[/FONT]
[FONT=courier new]    .Run "'" & rp & "'!rpt_Run", True[/FONT]
[FONT=courier new]
[/FONT]
[FONT=courier new]    If Err.Number <> 0 Then[/FONT]
[FONT=courier new]        Dim fso, fil, err_Msg, err_Fol, err_Fil[/FONT]
[FONT=courier new]
[/FONT]
[FONT=courier new]        err_Msg = Err.Number & " : " & Err.Description[/FONT]
[FONT=courier new]        err_Fol = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"[/FONT]
[FONT=courier new]        err_Fil = err_Fol & "Error.txt"[/FONT]

[FONT=courier new]        Set fso = CreateObject("Scripting.FileSystemObject")[/FONT]
[FONT=courier new]        Set fil = fso.CreateTextFile(err_Fil, True)[/FONT]
[FONT=courier new]        fil.WriteLine("Error: " & err_Msg)[/FONT]
[FONT=courier new]        fil.Close[/FONT]

[FONT=courier new]        Set fil = Nothing[/FONT]
[FONT=courier new]        Set fso = Nothing[/FONT]
[FONT=courier new]    End If[/FONT]
[FONT=courier new]
[/FONT]
[FONT=courier new]    For Each wb In .Workbooks[/FONT]
[FONT=courier new]        wb.Close False[/FONT]
[FONT=courier new]    Next[/FONT]
[FONT=courier new]
[/FONT]
[FONT=courier new]    .Quit[/FONT]
[FONT=courier new]End WIth[/FONT]
[FONT=courier new]
[/FONT]
[FONT=courier new]Set xl = Nothing[/FONT]
[FONT=courier new]WScript.Quit[/FONT]

 

[mrmmickle1]

svetovich,

Welcome to the board.

What code are you running in the macro?

 

[svetovich]

Hi mrmmickle1,

The code in the macro is incredibly long and involves a number of different procedures. While I was doing my testing I put a ThisWorkbook.Save call as the first line in the Workbook_Open() event and it hit the error immediately, so I'm fairly 100% confident that the issue with the .Save call.

 

[mrmmickle1]

Hmmm.....

Try using

ActiveWorkbook.Save

instead.....

 

[svetovich]

After further testing and checking the Event Viewer, it's looking like the workbook isn't actually opening. The .vbs file is creating the opening excel because I can see it opening in the Task Manager but then the workbook itself never opens. Why would the .vbs file and the workbook function differently when the scheduled task is set to run when logged off vs being set to run only when a user is logged in?

 

[mrmmickle1]

I've used this. .vbs file to open an Access Database from Task Scheduler successfully... You can probably modify it to do the same with excel.

[COLOR=#0000ff]Option Explicit[/COLOR]
[COLOR=#0000ff]Dim [/COLOR]oShell
[COLOR=#008000]'open the Access file[/COLOR]
Set oShell = CreateObject("WScript.Shell")
oShell.Run """C:\Program Files\Microsoft Office\Office15\MSACCESS.EXE"" ""C:\Users\mmickle\Desktop\Managed_Care_Database.accdb"""

 

[svetovich]

Would I also be able to use that to run a specific macro within the workbook after it's been opened? The reason I need this is when the workbook runs via the scheduled task I want the macro to trigger immediately, but if a user opens the workbook then I don't want it to run to the users can modify the workbook. This is the reason I can't use the Workbook_Open() event to trigger the macro.

 

[mrmmickle1]

You could use an if statement in the Workbook Open Event

[COLOR=#0000ff]If [/COLOR]Environ("UserName") <> "TASKSCHEDULER USER" [COLOR=#0000ff]Then[/COLOR]
[COLOR=#0000ff]Exit Sub
Else[/COLOR]
[COLOR=#008000]'Execute This Script[/COLOR]
[COLOR=#0000ff]End If[/COLOR]

If that doesn't do the trick you may try looking at this website for sample code...CLICK HERE

 

[svetovich]

I thought of that but users (myself included) frequently log in under the same account that the scheduled task is set to run under.

Also, I found that thread when I was doing my initial research.

The odd thing about my issue is that if I run the vbs file manually or the scheduled task is set to run only when a user is logged on then everything runs as expected. It's only when the scheduled task is set to run when a user is logged off that I'm getting the error in my original post.

 

[mrmmickle1]

Not to sure where to go from here....I don't have much experience with .VBS files.

Maybe another Forum Member will have additional guidance. Keep us posted on a resolution if you find one.