Transfering Code from 1 book to another?

[Paul Watkins]

I Have this piece of code that will transfer a Macro from One Book to
another Book (Book1.xls).
Book1.xls will then be closed.

ThisWorkbook.VBProject.VBComponents("Module1").Export "macro1.bas"
Workbooks("Book1.xls").VBProject.VBComponents.Import "macro1.bas"
Kill "macro1.bas"

At this point, Book1.xls has 1 module in it and no code under 'ThisWorkbook'


When I re-open 'Book1.xls' I need that macro to run automatically.
Normally this could be done by writing this line in 'ThisWorkbook'

Private Sub Workbook_Open()
Call macro1
End Sub

How do i get the above code into the microsoft excel object ' ThisWorkbook'
so that my macro will run automatically

Paul
(Getting more confused by the minute )

 

[Ron de Bruin]

Try something like this

Sub test()
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod =
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Private Sub Workbook_Open()" & Chr(13) & _
"Call macro1" & Chr(13) & _
"End Sub"
End With
End Sub

See Chip Pearson's site for more info
http://www.cpearson.com/excel/vbe.htm

 

[Paul Watkins]

Thanks Ron, I'll give it a go

Try something like this

Sub test()
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod =
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Private Sub Workbook_Open()" & Chr(13) & _
"Call macro1" & Chr(13) & _
"End Sub"
End With
End Sub

See Chip Pearson's site for more info
http://www.cpearson.com/excel/vbe.htm

 

[Paul Watkins]

I've tried the code and it works, however the visual basic window remains
open in another workbook.
How do i write the code to close the visual basic editor?


Thanks
Paul

 

[Ron de Bruin]

Try this Paul

Application.VBE.MainWindow.Visible = False

 

[Paul Watkins]

Thanks again Ron, it works great

Paul

Try this Paul

Application.VBE.MainWindow.Visible = False