VBA-在宏中执行或运行.bat文件 [英] VBA - Execute or Run .bat File Inside Macro

问题描述

我一直在尝试通过创建脚本和宏等为我工作的某些用户自动化一系列管理事件.

I have been attempting to automate a series of administrative events for some of the users where I work by creating scripts and macro's and so on..

这些脚本和宏可以很好地工作，但是，我想通过运行一个将系统地执行脚本和宏的批处理文件来使用户的工作变得更加容易.

These scripts and macros work great, however, I would like to make a the process even easier for the users by running a single batch file that will systematically execute the scripts and macros.

我当前拥有的批处理文件，一个接一个地调用所有脚本，最后一个脚本打开其中包含几个宏的xlsm工作簿之一-这就是问题所在-仍然有脚本要可以执行，但只能在此工作簿执行完所有宏后才能执行.

The batch file I currently have, calls all the scripts one by one, and the very last script opens one of the xlsm workbooks which contains a few macro's in it - and here is where the issue is - There are still scripts to be executed but they can only be executed once this workbook has executed all its macros.

所以我最初的想法是测试工作簿是否打开，如果是，将下一脚本的执行延迟一分钟左右，然后一次又一次地测试...直到关闭.也许从宏中执行下一组脚本(同样在批处理文件中)会更容易.

So my initial thought was to test if the workbook is open, if it is, delay the execution of the next script by a minute or so, then test again and again... until it is closed.. Then I thought perhaps it would be easier to execute the next set of scripts (also in a batch file) from within a macro.

所以，我有以下代码:

Sub Run_BAT()

    Set obj = CreateObject("Wscript.Shell")

    obj.Run Chr(34) & "X:\Test\" & "Termination Reports Scripts\" & "Execute_Terminations.bat" & Chr(34), 0, True

    Set obj = Nothing

End Sub

哪个给我一个错误:

权限被拒绝

Permission Denied

然后是这段代码:

Sub WriteAndRunBatFile()

Call Shell("X:\Test\Termination Reports Scripts\Execute_Terminations.bat")

End Sub

哪个给我错误:

无效的过程调用

Invalid procedure call

任何包含"Shell"命令的代码示例都会出现此错误.

Any and every single code sample that contains the "Shell" command gives this error.

推荐答案

我的理论是您在应用程序中缺少对Windows脚本宿主对象模型的引用.

My theory is you're missing a reference in your application to the Windows Script Host Object Model.

在VBA编辑器中，转到工具"，参考"，确保已勾选.

In the VBA Editor, go to Tools, References, make sure that one's ticked.

出于安全原因，默认情况下未选中它-想象在Microsoft Office应用程序的每个实例中意外访问命令提示符...！

It's not ticked by default for security reasons - imagine unintended access to the command prompt in every instance of a Microsoft Office application...!

这篇关于VBA-在宏中执行或运行.bat文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！