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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
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 |