Microsoft KB Archive/245801

= XL2000: Sample Macros to Create a Module from an Existing Module, Named Range, or Text File =

Article ID: 245801

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q245801



SUMMARY
You may want to programmatically insert stored Visual Basic for Applications (VBA) code into a new workbook that you create with the Workbooks.Add method. This may be necessary if you programmatically create new workbooks that you want to contain both data and macros.

This article describes automated methods that you can use to take VBA code that is stored in a module in another open workbook, a named range on a spreadsheet, or in a text file, and insert it into a new blank workbook.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. NOTE: The following methods require either an existing workbook with stored code in a module or named spreadsheet range, or a saved text file containing the stored code.

All examples below require a reference to Microsoft Visual Basic for Applications Extensibility. To create this reference, in the Visual Basic Editor, click References on the Tools menu, and then click to select the Microsoft Visual Basic for Application Extensibility 5.3 check box.

To Insert Code from an Existing Module
  In an existing workbook, insert the following code: Sub ExportCodeMod

' Dimension variables Dim strCode As String Dim vbCom As VBComponent Dim modObj As Object

' Set object to the module you want to export. Set modObj = _ Application.VBE.ActiveVBProject.VBComponents.Item("modTest") ' Place code in a string. strCode = modObj.CodeModule.Lines(1, modObj.CodeModule.CountOfLines) ' Create new workbook. Application.Workbooks.Add ' Create a new module in workbook. Application.VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule) ' Add code to new module from string variable. Application.VBE.ActiveVBProject.VBComponents.Item("Module1") _ .CodeModule.AddFromString (strCode)

End Sub  When you run ExportCodeMod, it creates a new workbook with a module named Module1, that contains the code from the existing module, named modTest.

To test this sample code, you need to rename a module "modTest". To rename an existing module, in the Visual Basic Editor, select the module in the Project Explorer window; then, in the Properties Window, type a new name for the module. If you do not see the Project Explorer window or the Properties Window, click these window names on the View menu in the Visual Basic Editor.

To Insert Code from a Named Range
 In the Visual Basic Editor, copy the existing macro (VBA) code that you want to insert into the new workbook. Switch to Excel by pressing ALT+F11, or by clicking Microsoft Excel on the View menu, and then paste the code into a worksheet. Select the entire range that contains the macro code. On the Insert menu, point to Name, and then click Define.</li> Type a name for the range, such as MacroCode, and click OK.</li> Switch back to the Visual Basic Editor by pressing ALT+F11, or by pointing to Macro on the Tools menu, and then clicking Visual Basic Editor.</li>  Type the following code into a module in the existing workbook: Sub ExportCodeNR

' Dimension variable Dim strCode As String

' Read code from named range and store in variable For Each cl In Range("MacroCode") strCode = strCode & cl.Value & Chr$(10) Next

' Create new workbook. Application.Workbooks.Add ' Create a new module in workbook. Application.VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule) ' Add code to new module from string variable. Application.VBE.ActiveVBProject.VBComponents.Item("Module1") _ .CodeModule.AddFromString (strCode)

End Sub </li> When you run the ExportCodeNR macro, it creates a new workbook containing a module called Module1, and then inserts the code located within the named range "MacroCode".</li></ol>

To Insert Code from a Text File
 In a text file (this example uses C:\Code.txt), paste the macro (VBA) code that you want to insert in the new workbook. Then, save and close the text file.</li>  Type the following code into a module in the existing workbook.

To do this without programmatically reading through the text file, you can use the AddFromFile method, as follows: Sub ExportCodeTXT2

' Create new workbook. Application.Workbooks.Add ' Create a new module in workbook. Application.VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule) ' Add code to new module from string variable. Application.VBE.ActiveVBProject.VBComponents.Item("Module1") _ .CodeModule.AddFromFile ("C:\Code.txt")

End Sub A new workbook is created, containing a module named Module1, which contains the code from the text file. To achieve the same result by programmatically reading through the text file line-by-line, you can use the AddFromString method, as follows: Sub ExportCodeTXT

' Dimension variables Dim strCode, strLine As String Dim FileNum As Integer

' Store the path and file name of text file. FileName = "C:\Code.txt" ' Get next available file handle number. FileNum = FreeFile ' Open text file for input. Open FileName For Input As #FileNum ' Loop until the end of file is reached. Do While Seek(FileNum) <= LOF(FileNum) ' Store one line of text from file to variable. Line Input #FileNum, strLine strCode = strCode & strLine & Chr$(10) Loop

' Close the open text file. Close

' Create new workbook. Application.Workbooks.Add ' Create a new module in workbook. Application.VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule) ' Add code to new module from string variable. Application.VBE.ActiveVBProject.VBComponents.Item("Module1") _ .CodeModule.AddFromString (strCode)

End Sub </li></ol>

A new workbook is created, with a module named Module1, which contains the code from the text file.

<div class="references_section">