使用Excel vba宏运行Windows计划任务 [英] Using Excel vba Macro to be run through Windows Schedule Task

问题描述

我有一个Excel电子表格，我已经设置了一个计时器来运行代码到数据库。如果电子表格是打开的，现在是时间段内设置的时间，那么它将数据导出到数据库中

我在我的子例程和workbook_open中使用这一行 Application.OnTime TimeValue（22:00:00），ExportOpenJobs

电子表格是打开的，但我希望能够通过Windows计划任务进行设置。

我使用Windows Server 2012作为主机，文件在哪里被存储。在任务计划程序中，我将Action启动程序和程序脚本设置为位置和实际的* .xlsm文件以及任务的开始时间。我在Excel VBA之前的30秒内设置了这个任务。

我的问题是Windows任务计划程序在设置的时候运行，看完任务历史后，我可以请参阅任务开始/完成和操作开始/完成通常需要大约50分钟完成，但是当我检查数据库时，Excel VBA没有运行。

我如何得到我的任务计划程序来运行Excel VBA代码？

在Windows Server中实际需要安装Excel，因此应该在另一台机器上完成？ p>

解决方案

你好，我要做的是创建这个.VBS文件，然后使用Windows任务计划程序以期望的间隔执行这个vbs文件。

 设置fso = CreateObject（Scripting.FileSystemObject）
 curDir = fso.GetAbsolutePathName（。）
 
设置myxlApplication = CreateObject（Excel.Application）
 myxlApplication.Visible = False 
设置myWorkBook = myx lApplication.Workbooks.Open（curDir& \myTest.xlsm）'更改为具有宏
 myWorkBook.Application.Run的实际工作簿Module1.HelloWorld'更改为包含宏的模块和宏
 myxlApplication.Quit 
  

Application.Run方法（Excel）

此示例要求将Excel安装到运行计划任务的主机上。如果该主机是服务器或桌面计算机是您的选择。

Windows任务计划程序应该按照任务的Action部分完成：

操作：启动程序

程序/脚本：C：\Windows\SysWOW64\cscript.exe

添加参数（可选） ：C：\Path_to_your_vbs\Your.vbs

开始于（可选）：C：\Path_to_your_vbs \

I have a Excel spreadsheet which I've set up a timer to run code into a database. If the spreadsheet is open and the time now is the time set within the timeslot then it exports the data into the database

I use this line in both my subroutine and the workbook_open Application.OnTime TimeValue("22:00:00"), "ExportOpenJobs"

This is great for when the spreadsheet is open, but I want to be able to set it through the Windows Schedule task.

I'm using Windows Server 2012 as my host pc and where the file is stored. Within the Task Scheduler I set the Action to Start a program and the Program script to the location and the actual *.xlsm file along with the start time for the Task. I set this task 30seconds before the time within the Excel VBA.

My problem is that the Windows Task Scheduler runs at the time set, after looking at the Task History I can see the Task Started/Completed and Action Started/Completed often taking around 50mins to complete, but when I check the database the Excel VBA hasn't run.

How can I get my Task Scheduler to run the Excel VBA code?

Within the Windows Server do you actually need to have Excel installed, therefore should it be done on another machine?

解决方案

Hello what I would do is create this .VBS file and then use Windows Task Scheduler to execute this vbs file at the desired interval.

Set fso = CreateObject("Scripting.FileSystemObject")
curDir = fso.GetAbsolutePathName(".")

Set myxlApplication = CreateObject("Excel.Application")
myxlApplication.Visible = False
Set myWorkBook = myxlApplication.Workbooks.Open( curDir & "\myTest.xlsm" ) 'Change to the actual workbook that has the Macro
myWorkBook.Application.Run "Module1.HelloWorld" 'Change to the Module and Macro that contains your macro
myxlApplication.Quit

Application.Run Method (Excel)

This example does require Excel to be installed on to the host running the scheduled task. If that host is a Server or a Desktop computer is your choice.

The Windows Task Scheduler should be done as such for the Action portion of the task:

Action: Start a program

Program/script: C:\Windows\SysWOW64\cscript.exe

Add arguments (optional): C:\Path_to_your_vbs\Your.vbs

Start in (optional): C:\Path_to_your_vbs\

这篇关于使用Excel vba宏运行Windows计划任务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！