All geek questions in one place

Using Excel vba Macro to Run Through Windows Schedule Task

I have an Excel spreadsheet in which I set up a timer to run code in a database. If the spreadsheet is open, and now the time is the time set in the time interval, then it exports the data to the database

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

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

I use Windows Server 2012 as my computer and where the file is stored. In Task Scheduler, I set the action to run the program and the script program in the location and actual * .xlsm file along with the start time of the task. I set this task 30 seconds before the time in Excel VBA.

My problem is that the Windows Task Scheduler runs at a set time, after looking at the task history, I see that Task Started / Completed and Action Started / Completed often take about 50 minutes, but when I check the database, Excel VBA did not start.

How can I get my task scheduler to run Excel VBA code?

On Windows Server, you really need to install Excel, so should this be done on another computer?

+6
source share
2 answers

Hello, what would I do, create this .VBS file, and then use the Windows task scheduler to execute this vbs file at the right 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)

, Excel , . , .

Windows :

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\

+8

Source: https://habr.com/ru/post/1543916/

More articles:


All Articles