在不同的excel文件中使用一个宏

I wrote a macro, which I would like to use in different excel files, which have almost the same table structure but different data.

我编写了一个宏，我想在不同的excel文件中使用它们，它们的表结构几乎相同，但数据不同。

So is it possible anyhow just to "include" my macro-script to any excel file?!

那么，是否可以将我的宏脚本“包含”到任何excel文件中呢?

I read already this tip but it sounds for me like a bad joke.

我已经读过这个提示，但对我来说，它听起来像是一个糟糕的笑话。

Thank you

谢谢你！

5 个解决方案

#1

You need to save the document as an Excel Add-in (.xla) and distribute that. Users can then go Tools>Add-ins to install the add in. They will need to browse to the file you sent them and verify the add-in is checked on the add-in list. Note that add-ins do not show up in Alt+F8 or in Tools>Macro. You need to make a menu for that in your add-in code. see this post http://spreadsheetpage.com/index.php/tip/creating_custom_menus/

您需要将文档保存为Excel外接程序(.xla)并分发它。用户可以使用工具>插件来安装add in。他们需要浏览到您发送的文件，并检查加载项是否在加载项列表中进行了检查。注意，外接程序不显示在Alt+F8或工具>宏中。你需要在你的外接程序代码中为它做一个菜单。看到这篇文章http://spreadsheetpage.com/index.php/tip/creating_custom_menus/

If you want your code to be available in all your workbooks, then use your PERSONAL.XLS or in Excel 2007-2010 your PERSONAL.XLSB file. These are normally held in the XLSTART folder under your documents and settings.

如果您希望您的代码在所有工作簿中都可用，那么请使用您的个人信息。XLS或Excel 2007-2010。XLSB文件。这些通常保存在文档和设置下的XLSTART文件夹中。

This is a hidden workbook that opens when you start Excel. The code you copy in this workbook is available in all workbooks you have opened in Excel.

这是一个隐藏的工作簿，在您启动Excel时打开。您在本工作簿中复制的代码可以在您在Excel中打开的所有工作簿中找到。

The easiest way to make one is to record a dummy macro, then select Personal Macro Workbook under the Store The Macro in drop-down list.

最简单的方法是记录一个虚拟宏，然后在下拉列表中选择存储宏下的个人宏工作簿。

Open up macro editor (ALT+F9) and then save the PERSONAL.XLS file. write up a macro, e.g

打开宏编辑器(ALT+F9)，然后保存个人文件。XLS文件。写一个宏，例如

Public Sub Testing()
    MsgBox "Hey im from Personal.xls"
End Sub

Remember to hide the workbook named personal.xls (Window>>Hide). Now on any workbook, this macro is available.

记住要隐藏名为personal的工作簿。xls(Window > >隐藏)。现在，在任何工作簿上，这个宏都是可用的。

#2

You have two options; you can

你有两个选择;你可以

1) include your macro in your personal.xlsb, which will be hidden, yet available for your use or

1)把你的宏观性放在你的个人身上。xlsb，它将被隐藏，但您可以使用或

2) include your macro in an add-in file which will startup with excel every time. Add-in files give you the added benefit of easy distribution if someone else would like to use your macro(s).

2)将宏包含在一个外接程序文件中，该文件将在每次使用excel时启动。如果其他人想使用您的宏，外接程序文件可以为您提供轻松分发的附加好处。

For the tool I'm building, I chose to use an add-in file and it's worked out great.

对于我正在构建的工具，我选择使用一个外接程序文件，效果非常好。

Put your code in a module in the addin, make sure the macro is not private, and you can even make a button for easy use on your quick-access-toolbar.

将您的代码放在addin中的模块中，确保宏不是私有的，您甚至可以创建一个按钮，以便在快速访问工具栏上轻松使用。

Problem solved.

问题解决了。

#3

If the personnal.xls solution does not suit your needs, you can just save the macro(s) to an Excel workbook, and ask the users to have it loaded (open) while working in the "other" document.
You might need to correct your macro in that case. eg: replace Thisworkbook by Activeworkbook.

如果个人。xls解决方案不适合您的需要，您可以将宏保存到Excel工作簿中，并要求用户在“其他”文档中工作时加载(打开)它。在这种情况下，您可能需要更正您的宏。把这本工作簿换成活书。

#4

The simplest, best way to do this is to create a "commander" XLSM which contains the macro but none of the data. All it does is open the specified Excel files and run the macro on them. You can either type/paste in the name of a specific file(s), you can loop through an entire directory, etc. It is very easy to run a macro on a different workbook than the one which actually contains the macro. Something like this below... piece of cake.

最简单、最好的方法是创建一个包含宏但不包含任何数据的“commander”XLSM。它所做的就是打开指定的Excel文件并在其上运行宏。您可以键入/粘贴特定文件的名称，也可以循环整个目录，等等。在不同的工作簿上运行宏比在实际包含宏的工作簿上运行宏要容易得多。这样下面……块蛋糕。

Sub MultipleFileMacro()

Dim wb As Excel.Workbook
Dim fso As Scripting.FileSystemObject
Dim f As Scripting.File

   Set fso = New Scripting.FileSystemObject
   For Each f In fso.GetFolder("c:\samplefolder").Files
      Set wb = Excel.Workbooks.Open(f)

      MyMacro wb

      wb.Close
   Next f

End Sub
------------
Sub MyMacro(wb As Excel.Workbook)

   'do something here

End Sub

#5

If you want to use the Macro in more than one workbook, either convert it to AddIn or save in Personal. Xlsb file,, and if the Database structure is similar in other Workbooks, your Macro will be useful, but always remember each code has a specific task, ☺

如果您想在多个工作簿中使用宏，可以将其转换为AddIn或save in Personal。Xlsb文件,如果数据库结构是类似的其他练习册,你的宏会有用,但是永远记得每个代码都有一个特定的任务,☺

#1