Using a macro to create a macro in another workbook
 

You're right Bernie - as soon as it starts to insert the code Excel crashes.

Does anyone have any other suggestions?

"Bernie Deitrick" wrote:

Giz,

You can do it with code, but I often have Excel die when doing so.

That said, try the macro below, with the activeworkbook being workbook A or B - I wasn't sure which
needs the new sub. And note that, as written, the code will remove any code in the ThisWorkbook
object's codemodule....

Also, this code requires a reference to MS VBA Extensibility.

HTH,
Bernie
MS Excel MVP

Sub Test()
Dim myBook As Workbook
Dim myVBA As VBIDE.VBComponent
Set myBook = ActiveWorkbook
Set myVBA = myBook.VBProject.VBComponents(myBook.CodeName)

On Error GoTo NoLines
With myVBA.CodeModule
.DeleteLines 1, .CountOfLines
End With

NoLines:

With myVBA.CodeModule
.InsertLines 1, "Private Sub Workbook_Open()" & Chr(10) & _
"Msgbox ""Hi there from your new macro""" & Chr(10) & _
"End Sub"
End With

End Sub


"Gizmo63" wrote in message
...
Here's the challenge.
The user's workbooks all rely on macros.
To simplify updating of macros and bug fixing all the macros are kept in a
central storage file.

"workbook A" has it's buttons linked to "Macro Storage" as does "workbook B"
etc etc
So when the user hits a button in "workbook A" it opens "Macro Storage" as
read-only and executes the macro.

The mods that I'm making require some coding to be added to the users
workbook. (Essentially a run-on-load subroutine).

When the mod runs from "Macro Storage" is there any way to create a
"Private Sub Workbook_Open()" in the users workbook and insert the code?

Thanks in advance.

Giz

Using a macro to create a macro in another workbook
 

Gizmo,

The better way to do it is to have a template with all the code in it already, open the template,
copy the worksheets over from the existing workbook, kill the existing workbook, and save the
template with the original workbook's name. Keeps Excel from crashing.

Let us know if you need help with the code.

HTH,
Bernie
MS Excel MVP


"Gizmo63" wrote in message
...
You're right Bernie - as soon as it starts to insert the code Excel crashes.

Does anyone have any other suggestions?

"Bernie Deitrick" wrote:

Giz,

You can do it with code, but I often have Excel die when doing so.

That said, try the macro below, with the activeworkbook being workbook A or B - I wasn't sure
which
needs the new sub. And note that, as written, the code will remove any code in the ThisWorkbook
object's codemodule....

Also, this code requires a reference to MS VBA Extensibility.

HTH,
Bernie
MS Excel MVP

Sub Test()
Dim myBook As Workbook
Dim myVBA As VBIDE.VBComponent
Set myBook = ActiveWorkbook
Set myVBA = myBook.VBProject.VBComponents(myBook.CodeName)

On Error GoTo NoLines
With myVBA.CodeModule
.DeleteLines 1, .CountOfLines
End With

NoLines:

With myVBA.CodeModule
.InsertLines 1, "Private Sub Workbook_Open()" & Chr(10) & _
"Msgbox ""Hi there from your new macro""" & Chr(10) & _
"End Sub"
End With

End Sub


"Gizmo63" wrote in message
...
Here's the challenge.
The user's workbooks all rely on macros.
To simplify updating of macros and bug fixing all the macros are kept in a
central storage file.

"workbook A" has it's buttons linked to "Macro Storage" as does "workbook B"
etc etc
So when the user hits a button in "workbook A" it opens "Macro Storage" as
read-only and executes the macro.

The mods that I'm making require some coding to be added to the users
workbook. (Essentially a run-on-load subroutine).

When the mod runs from "Macro Storage" is there any way to create a
"Private Sub Workbook_Open()" in the users workbook and insert the code?

Thanks in advance.

Giz

Using a macro to create a macro in another workbook
 

Thanks Bernie, but I'll be fine with coding that.

Had hoped for a nice 'enclosed' solution but hey, can't have everything!?!

Cheers anyway

Giz

"Bernie Deitrick" wrote:

Gizmo,

The better way to do it is to have a template with all the code in it already, open the template,
copy the worksheets over from the existing workbook, kill the existing workbook, and save the
template with the original workbook's name. Keeps Excel from crashing.

Let us know if you need help with the code.

HTH,
Bernie
MS Excel MVP


"Gizmo63" wrote in message
...
You're right Bernie - as soon as it starts to insert the code Excel crashes.

Does anyone have any other suggestions?

"Bernie Deitrick" wrote:

Giz,

You can do it with code, but I often have Excel die when doing so.

That said, try the macro below, with the activeworkbook being workbook A or B - I wasn't sure
which
needs the new sub. And note that, as written, the code will remove any code in the ThisWorkbook
object's codemodule....

Also, this code requires a reference to MS VBA Extensibility.

HTH,
Bernie
MS Excel MVP

Sub Test()
Dim myBook As Workbook
Dim myVBA As VBIDE.VBComponent
Set myBook = ActiveWorkbook
Set myVBA = myBook.VBProject.VBComponents(myBook.CodeName)

On Error GoTo NoLines
With myVBA.CodeModule
.DeleteLines 1, .CountOfLines
End With

NoLines:

With myVBA.CodeModule
.InsertLines 1, "Private Sub Workbook_Open()" & Chr(10) & _
"Msgbox ""Hi there from your new macro""" & Chr(10) & _
"End Sub"
End With

End Sub


"Gizmo63" wrote in message
...
Here's the challenge.
The user's workbooks all rely on macros.
To simplify updating of macros and bug fixing all the macros are kept in a
central storage file.

"workbook A" has it's buttons linked to "Macro Storage" as does "workbook B"
etc etc
So when the user hits a button in "workbook A" it opens "Macro Storage" as
read-only and executes the macro.

The mods that I'm making require some coding to be added to the users
workbook. (Essentially a run-on-load subroutine).

When the mod runs from "Macro Storage" is there any way to create a
"Private Sub Workbook_Open()" in the users workbook and insert the code?

Thanks in advance.

Giz