MS Excel 自动化生成的报告中没有宏。有什么想法吗？
我知道网络上充满了这样的问题，但我仍然无法将我能找到的答案应用于我的情况。
我意识到有 VBA，但我总是不喜欢将程序/宏放在 Excel 文件中，从而导致膨胀、安全警告等。我正在考虑使用 VBScript 来处理一组 Excel 文件，同时让它们不受宏观影响。现在，我已经能够按照这种方法将目录中的所有文件“将第一列涂成蓝色”，但我需要执行更复杂的操作（图表、数据透视表等），这会困难得多（不可能？）使用 VBScript 比使用 VBA 更好。
对于这个特定的示例，知道如何在处理后从所有文件中删除所有宏就足够了，但欢迎所有建议。有什么好的参考资料吗？任何关于如何最好地处理 Excel 文件的外部批处理的建议将不胜感激。
谢谢！
PS：我热切地尝试了 Mark Hammond 出色的 PyWin32 软件包，但缺乏文档和解释器反馈让我泄气。
I know that the web is full of questions like this one, but I still haven't been able to apply the answers I can find to my situation.
I realize there is VBA, but I always disliked having the program/macro living inside the Excel file, with the resulting bloat, security warnings, etc. I'm thinking along the lines of a VBScript that works on a set of Excel files while leaving them macro-free. Now, I've been able to "paint the first column blue" for all files in a directory following this approach, but I need to do more complex operations (charts, pivot tables, etc.), which would be much harder (impossible?) with VBScript than with VBA.
For this specific example knowing how to remove all macros from all files after processing would be enough, but all suggestions are welcome. Any good references? Any advice on how to best approach external batch processing of Excel files will be appreciated.
Thanks!
PS: I eagerly tried Mark Hammond's great PyWin32 package, but the lack of documentation and interpreter feedback discouraged me.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以将宏放在单独的 Excel 文件中。
You could put your macros in a separate excel file.
几乎在 VBA 中可以执行的任何自动化 Excel 操作都可以在 VBScript（或支持 COM 的任何其他脚本/语言）中执行。
创建 Excel.Application 的实例后，您几乎可以将 VBA 放入 VBS 中并从那里开始。
Almost anything you can do in VBA to automate excel you can do in VBScript (or any other script/language that supports COM).
Once you have created an instance of Excel.Application you can pretty much drop your VBA into a VBS and go from there.
如果您希望使用 Excel/VBA 功能，那么您始终可以先创建与您要在 Excel 文件（一种主文件）中处理的 Excel 文件进行交互的所有代码。正如 Karsten W 建议的那样，与常规文件分开。
这使您可以自由地编写 Excel/VBA。
然后，您可以从 VB 脚本、批处理文件、任务计划程序等调用您的主工作簿（例如，可以将其配置为在打开书本时运行您的代码）。
如果您想要更有趣，您甚至可以使用 VBA在您的主文件中创建/修改/删除您正在处理的任何目标文件中的自定义宏/VBA 模块。
我所描述的几乎所有技术的信息都是从 Excel VBA 内置参考文档中获得的，但这无疑有助于熟悉您正在处理的特定编程任务。我建议最好的方法是将您的任务（例如，将列设为蓝色、更新/排序数据等）一一放在一起，然后担心最后的自动化。
If it's the Excel/VBA capability that you're looking to use then you could always start by creating all of the code that will interact with the Excel files you're wanting to work on within an Excel file - a kind of master file that is separated from the regular files, as suggested by Karsten W.
This gives you the freedom to write Excel/VBA.
Then you can call your master workbook (which can be configured to run your code when the book is opened, for example) from a VB script, batch file, Task Scheduler, etc.
If you want to get fancy, you can even use VBA in your master file to create/modify/delete custom macros/VBA modules in any of the target files that you're processing.
The info for just about all of the techniques I'm describing I got from the Excel VBA built-in reference docs, but it certainly helps to be familiar with the specific programming tasks that you're tackling. I'd advise that the best approach is to put together your tasks (eg, make column blue, update/sort data etc) one by one and then worry about the automation at the end.