Use a module for all excel files in a folder

Question

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?

Answer 1

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