Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gizmo63
 
Posts: n/a
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
 
Posts: n/a
Default 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




  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gizmo63
 
Posts: n/a
Default 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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
run macro on workbook close Nigel Excel Discussion (Misc queries) 3 November 29th 05 09:48 PM
Is it possible to create a workbook within a workbook? mwilliams Excel Worksheet Functions 2 November 29th 05 08:27 PM
Macro Help In Excel welshlad Excel Discussion (Misc queries) 14 October 26th 05 02:34 PM
Extract specific data into its own workbook via macro? Adrian B Excel Discussion (Misc queries) 2 February 24th 05 07:09 AM
Copying a workbook with custom toolbar assigned to a macro Matt W Excel Discussion (Misc queries) 1 February 4th 05 11:46 PM


All times are GMT +1. The time now is 03:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"