Can macro create another macro?
 

Hi, all!
An macro from Personal.xls create a new sheet, now i need another macro
available in that new sheet. How do I do it, that macro create another
macro?
Reg, VK

Can macro create another macro?
 

If it is an event macro, use something like

Dim iStartAs Long

With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
iStart = .CreateEventProc("BeforeSave", "Workbook") + 1
.InsertLines iStart, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With

If it just another macro in Module1 say, then use

Dim iNext As Long

With ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule
iNext = .CountOfLines + 1
.InsertLines iNext, _
"Sub myProc()" & vbNewLine & _
" Msgbox ""Tesing new procedure"" " & vbNewLine & _
"End Sub"
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)


"VK" wrote in message
...
Hi, all!
An macro from Personal.xls create a new sheet, now i need another macro
available in that new sheet. How do I do it, that macro create another
macro?
Reg, VK

Can macro create another macro?
 

If it is an event macro, use something like

Dim iStartAs Long

With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
iStart = .CreateEventProc("BeforeSave", "Workbook") + 1
.InsertLines iStart, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With

If it just another macro in Module1 say, then use

Dim iNext As Long

With ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule
iNext = .CountOfLines + 1
.InsertLines iNext, _
"Sub myProc()" & vbNewLine & _
" Msgbox ""Tesing new procedure"" " & vbNewLine & _
"End Sub"
End With


Thanks! I'll try it out
Reg. VK

Can macro create another macro?
 

"VK" wrote in message
...
If it is an event macro, use something like

Dim iStartAs Long

With
ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
iStart = .CreateEventProc("BeforeSave", "Workbook") + 1
.InsertLines iStart, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With

If it just another macro in Module1 say, then use

Dim iNext As Long

With ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule
iNext = .CountOfLines + 1
.InsertLines iNext, _
"Sub myProc()" & vbNewLine & _
" Msgbox ""Tesing new procedure"" " & vbNewLine & _
"End Sub"
End With


Thanks! I'll try it out
Reg. VK

Would like todo same with about 5 multi-page routines, so wonder if instead
of embedding the new code in the copying macro (per above), if its possible
to copy an existing macro between files, say from personal.xls to book1.xls
??

ie:

Sub test()
Workbooks("Book1").VBProject.VBComponents.Import _
(Workbooks("Personal").VBProject.vbcompoents("agin g_report").codemodule)
End Sub

seems logical, but returns "runtime error 438: object does not support this
property or method"

Can macro create another macro?
 

You cannot import a module from another workbook, you must import from a
file. So you export from Personal.xls to a file, and then import that file
into another book.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jef Gorbach" wrote in message
...

"VK" wrote in message
...
If it is an event macro, use something like

Dim iStartAs Long

With
ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
iStart = .CreateEventProc("BeforeSave", "Workbook") + 1
.InsertLines iStart, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With

If it just another macro in Module1 say, then use

Dim iNext As Long

With ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule
iNext = .CountOfLines + 1
.InsertLines iNext, _
"Sub myProc()" & vbNewLine & _
" Msgbox ""Tesing new procedure"" " & vbNewLine & _
"End Sub"
End With


Thanks! I'll try it out
Reg. VK

Would like todo same with about 5 multi-page routines, so wonder if
instead
of embedding the new code in the copying macro (per above), if its
possible
to copy an existing macro between files, say from personal.xls to
book1.xls
??

ie:

Sub test()
Workbooks("Book1").VBProject.VBComponents.Import _

(Workbooks("Personal").VBProject.vbcompoents("agin g_report").codemodule)
End Sub

seems logical, but returns "runtime error 438: object does not support
this
property or method"

Can macro create another macro?
 

Bob Phillips wrote:
If it is an event macro, use something like

Dim iStartAs Long

With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
iStart = .CreateEventProc("BeforeSave", "Workbook") + 1
.InsertLines iStart, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With

If it just another macro in Module1 say, then use

Dim iNext As Long

With ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule
iNext = .CountOfLines + 1
.InsertLines iNext, _
"Sub myProc()" & vbNewLine & _
" Msgbox ""Tesing new procedure"" " & vbNewLine & _
"End Sub"
End With


It works with simple macros. I'm using on another mashine some add-on
for Excel with special code to interact with another application. When
"parent" macro try to append another macro to VB Project, I'm getting
errors. It cannot compile unknown commands from add-on. Is it possible
to disable compilation for this part of code?

Thanks anyway!

Reg, VK

Can macro create another macro?
 

Why not load add-in?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"VK" wrote in message
...
Bob Phillips wrote:
If it is an event macro, use something like

Dim iStartAs Long

With
ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
iStart = .CreateEventProc("BeforeSave", "Workbook") + 1
.InsertLines iStart, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With

If it just another macro in Module1 say, then use

Dim iNext As Long

With ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule
iNext = .CountOfLines + 1
.InsertLines iNext, _
"Sub myProc()" & vbNewLine & _
" Msgbox ""Tesing new procedure"" " & vbNewLine & _
"End Sub"
End With


It works with simple macros. I'm using on another mashine some add-on
for Excel with special code to interact with another application. When
"parent" macro try to append another macro to VB Project, I'm getting
errors. It cannot compile unknown commands from add-on. Is it possible
to disable compilation for this part of code?

Thanks anyway!

Reg, VK

Can macro create another macro?
 

Because it is on another machine

Can macro create another macro?
 

This is a page on Chip Pearson's website which discusses copying macro
between workbooks which you both may find helpful

http://www.cpearson.com/excel/vbe.htm

Hope this helps

--
bhofset
-----------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880
View this thread: http://www.excelforum.com/showthread.php?threadid=37436

Can macro create another macro?
 

I just tried to reproduce what you get but I couldn't.

Can you post the code that failed so that I can try it? I have a couple of
ideas.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Onfrek" wrote in message
oups.com...
Because it is on another machine

Can macro create another macro?
 

Bob Phillips wrote:
I just tried to reproduce what you get but I couldn't.

Can you post the code that failed so that I can try it? I have a couple of
ideas.


Unfortunately, I can't.
I solved the problem, copying data from one worksheet with first macro
to another worksheet, where second macro already exist. Thanks for your
help.
Reg. VK

Can macro create another macro?
 

Bob Phillips wrote:
I just tried to reproduce what you get but I couldn't.

Can you post the code that failed so that I can try it? I have a couple of
ideas.



This is the code to append



Sub ScreenPrint()
lpr = DDEInitiate("win", "wwk")
Call DDEExecute(lpr, "lpr-0" & ActiveCell.Text & "PRINT")
Call DDETerminate(lpr)
End Sub

Cheers, VK