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

Module question
 

This will import a file name ThisWorkbook.cls into the ThisWorkbook code
module of the workbook WB.

Sub AAA()
Dim FName As String
Dim FNum As Integer
Dim WB As Workbook
Dim S As String
Dim N As Long
FName = "C:\Temp\ThisWorkbook.cls" ' <<< CHANGE AS REQUIRED
Dim CodeMod As Object
Set WB = ActiveWorkbook '<<< CHANGE AS REQUIRED

FNum = FreeFile
Open FName For Input Access Read As #FNum
Set CodeMod = WB.VBProject.VBComponents("ThisWorkbook").CodeModu le
With CodeMod
.DeleteLines 1, .CountOfLines
End With

With CodeMod
''''''''''''''''''''''''''''''''
' skip the module heading info
''''''''''''''''''''''''''''''''
For N = 1 To 9
Line Input #FNum, S
Next N
N = 0
''''''''''''''''''''''''''''''''
' Import the code
''''''''''''''''''''''''''''''''
Do Until EOF(FNum)
Line Input #FNum, S
N = N + 1
.InsertLines N, S
Loop
End With
Close #FNum
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"C_Ascheman" wrote in message
...
Worked very well Chip. Unfortunately it placed it as a Class module
instead
of replacing the ThisWorkbook module which is what I need. Is there a way
to
do that or to place a line of code into the ThisWorkbook module to run the
class module when the workbooks loads (ie Call ThisWorkbook1 in the
Workbook_Open section of the ThisWorkbook module).?

C_Ascheman


"Chip Pearson" wrote:

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