Add module on all existing excel files.

[disk0nek]

I have created a module with contains some format
restructuring of cells and I want to use this on all my
existing excel file... Is there a way to this all at the
same time or I really have to the process of Open the
file hit Alt+F11 insert module and paste the code then
save?


thanks!

 

[Rob van Gelder]

This will add the file (cModule) into all *.xls files found in C:\T\ (cPath)
and Save/Close

Sub test()
Const cPath = "C:\T\", cModule = "C:\T\Module2.bas"
Dim strFile As String, wkb As Workbook

strFile = Dir(cPath & "*.xls")

Do Until strFile = ""
Set wkb = Workbooks.Open(cPath & strFile)
wkb.VBProject.VBComponents.Import cModule
wkb.Save
wkb.Close
strFile = Dir
Loop
End Sub

 

[Paul Robinson]

Hi,
Save the file containing the module as an .xla file (set the Is AddIn?
property to true in the ThisWorkBook module window). Check that the
code in the module refers to the "ActiveWorkBook" when dealing with
WorkBook objects

e.g. ActiveWorkBook.Worksheets("Sheets1"). etc
Any objects you need in the workbook that contains the code module
should be prefixed by "ThisWorkBook" e.g. formatting instructions
stored in its sheets that you need to copy to the ActiveWorkbook.

Save the AddIn in your AddIns folder and go to Tools...AddIns in Excel
to Register it. Commands in the AddIn will now be available and apply
to the active workbook.

regards
Paul