Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Module question
Try code like the following. Change the lines of code marked with <<< to
your own needs. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) Sub CopyModule() Dim SourceModuleFile As String Dim FName As String Dim ModuleNameToExport As String Dim DestinationFolder As String Dim SaveDir As String Dim WB As Workbook ''''''''''''''''''''''''''''''' ' Save the current directort ' setting. ''''''''''''''''''''''''''''''' SaveDir = CurDir '''''''''''''''''''''''''''''' ' Name of the module to ' export from ThisWorkbook. '''''''''''''''''''''''''''''' ModuleNameToExport = "Module1" '<<< CHANGE '''''''''''''''''''''''''''''' ' Filename of exported file. '''''''''''''''''''''''''''''' SourceModuleFile = ThisWorkbook.Path & "\" & ModuleNameToExport & ".bas" '''''''''''''''''''''''''''''' ' Kill the existing file if ' necessary. '''''''''''''''''''''''''''''' On Error Resume Next Kill SourceModuleFile On Error GoTo 0 '''''''''''''''''''''''''''''' ' Name of the folder containing ' all of the files into which ' the module will be imported. '''''''''''''''''''''''''''''' DestinationFolder = "C:\Temp" '<<<<<<< CHANGE '''''''''''''''''''''''''''''' ' Export the module: '''''''''''''''''''''''''''''' ThisWorkbook.VBProject.VBComponents(ModuleNameToEx port).Export _ FileName:=SourceModuleFile '''''''''''''''''''''''''''''' ' Change directory to ' DestinationFolder '''''''''''''''''''''''''''''' ChDrive DestinationFolder ChDir DestinationFolder '''''''''''''''''''''''''''''' ' Loop through files in ' DestinationFolder '''''''''''''''''''''''''''''' FName = Dir("*.xls") Do Until FName = vbNullString If CurDir & ThisWorkbook.Name < ThisWorkbook.FullName Then Set WB = Workbooks.Open(FileName:=FName) If WB.VBProject.Protection = 0 Then On Error Resume Next Err.Clear WB.VBProject.VBComponents.Import _ FileName:=SourceModuleFile If Err.Number Then Debug.Print "Could not import into " & WB.Name & "." & vbCrLf & _ "Probably 'Access To VBAProject' is not allowd." End If WB.Close savechanges:=True End If End If FName = Dir() Loop '''''''''''''''''''''''''''''' ' Restore directory '''''''''''''''''''''''''''''' ChDrive SaveDir ChDir SaveDir End Sub "C_Ascheman" wrote in message ... Is there a way to copy the module from the master workbook to all other workbooks in a folder? C_Ascheman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Module question | Excel Programming | |||
Class Module Question | Excel Programming | |||
Class module question | Excel Programming | |||
ThisWorkbook module question | Excel Programming |