在多个文件上运行excel宏 [英] Run excel macro on multiple files

问题描述

我有一个excel宏保存在一个空白的工作簿和多个数据工作簿中。

我目前分别打开宏文件和每个数据文件，每个宏都运行宏有一个键盘快捷键。

有没有办法在所有数据工作簿上运行宏，而不打开它们，无论是使用

• 一个批处理文件，


• VBA / VBScript，


• powershell，


• 或类似的东西

解决方案

你的宏的文件PERSONAL.XLSB。每次启动Excel时，该文件将在后台加载。最初，PERSONAL.XLSB文件不会在那里。
要自动创建此文件，只需开始录制一个虚拟宏（使用电子表格左下角的记录按钮），然后选择个人宏工作簿将其存储。记录您的宏后，您可以使用[Alt] + [F10]打开VBA编辑器，您将看到带有dummy宏的PERSONAL.XLSB文件。
我使用此文件来存储始终可用的一般宏的负载。我已经将这些宏添加到我自己的菜单功能区。
这个常见的宏文件的一个缺点是，如果您启动了多个Excel实例，则会收到一个错误消息，表明PERSONAL.XLSB文件已被Excel实例Nr使用。只要您现在不添加新的宏，这是没有问题的。

I have an excel macro saved in a blank workbook and multiple data workbooks.

I currently open the macro file and each data file individually, running the macro on each one with a keyboard shortcut.

Is there a way to run the macro on all the data workbooks without opening them, either with

• a batch file,
• VBA/VBScript,
• powershell,
• or something similar?

解决方案

One way to do this is to add your macro's to the file PERSONAL.XLSB. This file will be loaded in the background every time you start Excel. Initially the PERSONAL.XLSB file will NOT be there. To automatically create this file, just start recording a "dummy" macro (with the record button on the left-bottom of a spreadsheet) and select "Personal Macro Workbook" to store it in. After recording your macro, you can open the VBA editor with [Alt]+[F10] and you will see the PERSONAL.XLSB file with the "dummy" macro. I use this file to store loads of general macro's which are always available. I have added these macro's to my own menu ribbon. One disadvantage of this common macro file is that if you launch more than one instance of Excel, you will get an error message that the PERSONAL.XLSB file is already in use by Excel instance Nr. 1. This is no problem as long as you do not add new macro's at this moment.

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