通过Scheduler触发时，Excel中的VBA代码无法运行 [英] VBA code inside Excel doesn't run when triggered via Scheduler

问题描述

因此，在此WinServer 2012 R2 64bit上的设置为:

So the setup on this WinServer 2012 R2 64bit is:

Windows Task Scheduler-> cscript .vbs文件->打开excel并在主模块中运行一个子程序

Windows Task Scheduler -> cscript .vbs file -> opening excel and run a sub in the main module

当我双击.vbs文件时，这在后台运行良好，但是当我通过任务计划程序触发.vbs时，excel将打开，但不会加载文件或运行子文件(不确定哪个).该任务在对计算机具有管理权限的域用户下运行.当我尝试单击.vbs

This runs fine in the background when I double click the .vbs file, but when I trigger the .vbs via the task scheduler, excel opens, but doesn't load the file or run the sub (not sure which). The task runs under an domain user that has administration rights on the machine. I use the same user when i try clicking on the .vbs

正在运行的代码，顺序为:

Code that is being run, in order:

任务计划程序启动:

Task scheduler launches:

C:\ WINDOWS \ system32 \ cscript.exe"D:\ xyz \ trigger.vbs"

C:\WINDOWS\system32\cscript.exe "D:\xyz\trigger.vbs"

.vbs可以:

.vbs does:

Option Explicit

Dim xlApp, xlBook, xlsheets, xlcopy
Set xlApp = CreateObject("Excel.Application")
xlapp.Interactive = False
xlapp.DisplayAlerts = False
xlapp.AskToUpdateLinks = False
xlapp.AlertBeforeOverwriting = False
Set xlBook = xlApp.Workbooks.Open("D:\xyz\excelfile.xlsm")

On Error Resume Next
Call xlBook.Application.Run("Main.Extrnal_Trigger")

xlbook.Saved = True
xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlcopy = Nothing
Set xlApp = Nothing

WScript.Quit(1)

Excel代码:

Excel code:

Sub Extrnal_Trigger()

Application.EnableEvents = False
Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
Application.AlertBeforeOverwriting = False

Call update_button
Call MainProgram
Call ReportSave

End Sub

我如何找出.vbs或excel挂在哪里，为什么?在另一台机器上的非常相似的设置也可以正常运行.它实际上与此处引用的代码相同.

How can I find out where the .vbs or the excel hangs and why? A very similar setup on another machine does run without troubles. It is virtually identical to the code quoted here.

我意识到有几种不良做法(例如不清理xlapp设置)，但是我希望在清理之前先运行该过程.

I realize there are several bad practices (like not cleaning up xlapp settings), but I'd like to get the process running before cleaning up.

/删除

On Error Resume Next

.vbs中的

不会显示错误.

from the .vbs does not display an error.

/edit2:我尝试了尽可能远地还原.

/edit2: I tried reverting as far back as possible.

Option Explicit
Dim fso, f, s, log

Set fso = CreateObject("Scripting.FileSystemObject")

Set log = fso.CreateTextFile("D:\xyz\TESTlog.txt")
log.WriteLine "before fso"

Set f = fso.GetFile("D:\xyz\excel.xlsm")
s = f.Path & "  "
s = s & "Created: " & f.DateCreated & "  "
s = s & "Last Accessed: " & f.DateLastAccessed & "  "
s = s & "Last Modified: " & f.DateLastModified   

log.WriteLine "after fso"
log.writeline "fso content"
log.writeline s

当任务计划程序通过cscript.exe触发时，此方法有效.我将尝试修改以记录对excel文件的调用周围发生的情况.

This works when being triggered by the task scheduler via cscript.exe. I will try to modify to log what's happening around the call to the excel file.

/edit3:调试表明，这

/edit3: Debugging showed that this

Set xlBook = xlApp.Workbooks.Open("D:\xyz\excel.xlsm")

从不发生.我拨出错误编号，并为此电话收到错误1004.仍然不确定是什么问题，但是至少我现在得到一个错误号.

never happens. I put out error numbers and got error 1004 for this call. Still not sure what's the issue, but at least I got an error number now.

/edit4:

错误1004仍然存在.当我通过双击.vbs来运行它时，一切正常.

error 1004 when trying to run this as a scheduled tasks persists. When I am running it by double clicking the .vbs, everything works.

推荐答案

关键是要创建这两个文件夹:

C:\ Windows \ System32 \ config \ systemprofile \ Desktop

C:\Windows\System32\config\systemprofile\Desktop

和

C:\ Windows \ SysWOW64 \ config \ systemprofile \ Desktop

C:\Windows\SysWOW64\config\systemprofile\Desktop

当这些文件夹不存在(不确定原因)时，Excel在非交互模式下运行时显然会遇到麻烦.通过vbs打开工作簿时，创建它们摆脱了1004错误.

Excel apparently has troubles running in non-interactive mode when these folders are not present (not sure why). Creating them got rid ofthe 1004 error when opening the workbook via vbs.

这篇关于通过Scheduler触发时，Excel中的VBA代码无法运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！