Is it possible?

[atf32]

OS: Windows 7
MS Excel 2010

I have a file that saves out some of its worksheets to an external file and then saves that file as its own file. Code from the copied out sheets also copy out to the new file....Question: is is possible to save/export the code from the "ThisWorkbook" object from the VBE to the new file?

 

[atf32]

Actually, I described this wrong.......I want to know how to export a copy of the "ThisWorkbook.cls" to the new file.

 

[Sam_D_Ben]

ActiveWorkbook.SaveAs Filename:="C:\Users\user\Desktop\Book1.xls", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Try this code......Muz

 

[Damon Ostrander]

Hi atf32,

Yes, it is possible. Just as an example, here is code that will create a new workbook and copy the ThisWorkbook code from the workbook containing the code to the new workbook:

Sub CodeCopy()

   'Macro to create a new sheet and copy the macro module
   'from sheet1 to it.  Must install Microsoft Visual Basic
   'for Applications Extensibility library from Tools > References.
   
   Dim i          As Integer
   Dim NewWB      As Workbook
   Dim SrcCmod    As VBIDE.CodeModule
   Dim DstCmod    As VBIDE.CodeModule
   
   Set NewWB = Workbooks.Add

   Set SrcCmod = ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
   Set DstCmod = NewWB.VBProject.VBComponents("ThisWorkbook").CodeModule
   
   For i = 1 To SrcCmod.CountOfLines
      DstCmod.InsertLines i, SrcCmod.Lines(i, 1)
   Next i
   
End Sub

Please note that you must install Microsoft Visual Basic for Applications Extensibility library from Tools > References in the VBE.

 

[strive4peace]

from the Visual Basic Editor, choose File > Export
to save the code as a text file
I often save modules that I might want to use somewhere else this way

then when you want to use it in another workbook, go to the VBE and do File, Import

didn't see Damon's answer when I wrote this -- his way is much more elegant

 

[atf32]

Thanks, but this won't work....My file copies a number of tabs, which creates a default secondary file. My code then renames the seconadry file from the default filename......This all works fine. However, now I want to copy code that resides in the "ThisWorkbook.cls" (VBE). So that a "Workbook_BeforeClose" event executes in the new file.

 

[atf32]

OMG......Your good.....;o) This worked great!!!. Thanks.

 

[atf32]

Hi atf32,

Yes, it is possible. Just as an example, here is code that will create a new workbook and copy the ThisWorkbook code from the workbook containing the code to the new workbook:

Sub CodeCopy()

   'Macro to create a new sheet and copy the macro module
   'from sheet1 to it.  Must install Microsoft Visual Basic
   'for Applications Extensibility library from Tools > References.
   
   Dim i          As Integer
   Dim NewWB      As Workbook
   Dim SrcCmod    As VBIDE.CodeModule
   Dim DstCmod    As VBIDE.CodeModule
   
   Set NewWB = Workbooks.Add

   Set SrcCmod = ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
   Set DstCmod = NewWB.VBProject.VBComponents("ThisWorkbook").CodeModule
   
   For i = 1 To SrcCmod.CountOfLines
      DstCmod.InsertLines i, SrcCmod.Lines(i, 1)
   Next i
   
End Sub

Please note that you must install Microsoft Visual Basic for Applications Extensibility library from Tools > References in the VBE.

OMG......Your good.....;o) This worked great!!!. Thanks.​

 

[mikerickson]

This is a similar approach.
This does not require the Microsoft Visual Basic for Applications Extensibility library be open.

Sub test()
    Dim codeToWrite As String
    Dim newWorkbook As Workbook
    
    Set newWorkbook = Workbooks.Add
    
    With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
        codeToWrite = .Lines(1, .CountOfLines)
    End With
    
    With newWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
        .DeleteLines 1, .CountOfLines
        .AddFromString codeToWrite
    End With
End Sub

The .DeleteLines is to prevent double Option Explicit lines if your computer is configured to automatically insert them.