对文件夹中的所有Excel文件使用模块 [英] Use a module for all excel files in a folder

问题描述

我为excel文件创建了一个模块，该模块保存在系统的某个文件夹中.我希望能够将该模块用于将来几天将存储在该特定文件夹中的所有excel文件.诀窍在于，此模块不应应用于在该系统上创建的任何excel文件，而应仅应用于该特定文件夹中的所有excel文件. 我知道我可以将模块拖放到打开的工作簿中，但是我想知道是否有更自动化的方法来执行此操作. 能做到吗?

I have created a module for an excel file which is kept in a certain folder on the system. I want to be able to use that module for all the excel files that will be stored in that particular folder for the days to come. The trick is that this module should not be applied to any excel file that is created on that system, it should just applied to all the excel files in that particular folder. I know that I can drag and copy a module to the open workbooks but I want to know whether there is a more automated way to do this. Can this be done?

推荐答案

您可以以编程方式导入/导出模块，因此，例如，您可以编写一个小的宏，该宏将一个Excel文件复制到其中.复制模块的代码如下:

You can import/export modules programatically, so you could for example write a small Macro that takes an Excel file and copies your module into it. The code for copying modules is as follows:

Set myExportVBP = ThisWorkbook.VBProject
Set myImportVBP = Workbooks(ActiveWorkbook.Name).VBProject
With myImportVBP.VBComponents("Sheet1").CodeModule
    .DeleteLines 1, .CountOfLines
    .InsertLines 1, myExportVBP.VBComponents("Sheet1").CodeModule.Lines(1, myExportVBP.VBComponents("Sheet1").CodeModule.CountOfLines)
End With

这篇关于对文件夹中的所有Excel文件使用模块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！