Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
inset vba module using vba
Hi All,
Is it possible to insert a VBA module (which has been exported to say My Documents) into an existing workbook (say myBook1)and then run the macro in myBook1. I would like to control the operation from a "Control" workbook so that l can select the source & target multiple times as l need to add this VBA module to over 50 existing workbooks. Any idea's / example code greatly appreciated Regards Michael beckinsale. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
inset vba module using vba
Hi Michael,
Yes you can, I use such a method for generating a report and then inserting event trapping code in the workbook. I've pasted my code below that you can customise as you see fit. WARNING: If the code you insert doesn't compile properly Excel is very likely to crash. So be careful and save your work regularly - or prepare to cry in frustration. I believe Chip Pearson covers manipulating VBA thru VBA in depth at http://www.cpearson.com/excel.htm so you may like to take a look there. Function fcnInsertVBACodeIntoThisWorkbook(wb As Workbook, _ myFile As String) Dim myCode As String ' Insert this code into the ThisWorkbook code module With wb.VBProject.VBComponents(1).CodeModule .InsertLines 1, "'" 'seems to make it more stable .AddFromFile myFile End With End Function Function fcnInsertVBACodeIntoNewModule(wb As Workbook, _ myFile As String) Dim myCode As String Dim myMod As VBComponent 'create a new code module and write this code there Set myMod = wb.VBProject.VBComponents.Add(1) With myMod.CodeModule .InsertLines 1, "'" 'seems to make it more stable .AddFromFile myFile End With Set myMod = Nothing End Function HTH, Gareth michael.beckinsale wrote: Hi All, Is it possible to insert a VBA module (which has been exported to say My Documents) into an existing workbook (say myBook1)and then run the macro in myBook1. I would like to control the operation from a "Control" workbook so that l can select the source & target multiple times as l need to add this VBA module to over 50 existing workbooks. Any idea's / example code greatly appreciated Regards Michael beckinsale. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
inset vba module using vba
ActiveWorkbook.VBProject.VBComponents.Import Filename:="C:\myFile.bas"
-- HTH RP (remove nothere from the email address if mailing direct) "michael.beckinsale" wrote in message oups.com... Hi All, Is it possible to insert a VBA module (which has been exported to say My Documents) into an existing workbook (say myBook1)and then run the macro in myBook1. I would like to control the operation from a "Control" workbook so that l can select the source & target multiple times as l need to add this VBA module to over 50 existing workbooks. Any idea's / example code greatly appreciated Regards Michael beckinsale. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
inset vba module using vba
I would rather put such a macro in my Personal.xls Workbook and run th show from such focal point -- david ----------------------------------------------------------------------- davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064 View this thread: http://www.excelforum.com/showthread.php?threadid=39062 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
inset vba module using vba
Nearly forgot. You need to insert a reference to "Microsoft Visual Basic
for Applications Extensibility" in the workbook where you're running the below code. Maybe you can get away with late binding and avoid this but I've never tried and there's not much to be gained by that approach I imagine. Gareth wrote: Hi Michael, Yes you can, I use such a method for generating a report and then inserting event trapping code in the workbook. I've pasted my code below that you can customise as you see fit. WARNING: If the code you insert doesn't compile properly Excel is very likely to crash. So be careful and save your work regularly - or prepare to cry in frustration. I believe Chip Pearson covers manipulating VBA thru VBA in depth at http://www.cpearson.com/excel.htm so you may like to take a look there. Function fcnInsertVBACodeIntoThisWorkbook(wb As Workbook, _ myFile As String) Dim myCode As String ' Insert this code into the ThisWorkbook code module With wb.VBProject.VBComponents(1).CodeModule .InsertLines 1, "'" 'seems to make it more stable .AddFromFile myFile End With End Function Function fcnInsertVBACodeIntoNewModule(wb As Workbook, _ myFile As String) Dim myCode As String Dim myMod As VBComponent 'create a new code module and write this code there Set myMod = wb.VBProject.VBComponents.Add(1) With myMod.CodeModule .InsertLines 1, "'" 'seems to make it more stable .AddFromFile myFile End With Set myMod = Nothing End Function HTH, Gareth michael.beckinsale wrote: Hi All, Is it possible to insert a VBA module (which has been exported to say My Documents) into an existing workbook (say myBook1)and then run the macro in myBook1. I would like to control the operation from a "Control" workbook so that l can select the source & target multiple times as l need to add this VBA module to over 50 existing workbooks. Any idea's / example code greatly appreciated Regards Michael beckinsale. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
inset vba module using vba
And just as an added Specific reference/URL see Chip Pearson's page on this
http://www.cpearson.com/excel/vbe.htm -- Regards, Tom Ogilvy "michael.beckinsale" wrote in message oups.com... Hi All, Is it possible to insert a VBA module (which has been exported to say My Documents) into an existing workbook (say myBook1)and then run the macro in myBook1. I would like to control the operation from a "Control" workbook so that l can select the source & target multiple times as l need to add this VBA module to over 50 existing workbooks. Any idea's / example code greatly appreciated Regards Michael beckinsale. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
inset vba module using vba
This may not be an option, but if you're touching 50 different workbooks, you
may want to consider putting the code into an addin and sharing that addin. Then when the code needs to be changed, you fix the addin--instead of trying to fix all 50 workbooks. "michael.beckinsale" wrote: Hi All, Is it possible to insert a VBA module (which has been exported to say My Documents) into an existing workbook (say myBook1)and then run the macro in myBook1. I would like to control the operation from a "Control" workbook so that l can select the source & target multiple times as l need to add this VBA module to over 50 existing workbooks. Any idea's / example code greatly appreciated Regards Michael beckinsale. -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
inset vba module using vba
Hi All,
Many thanks for all your prompt responses. I will digest all the information you have provided and try out the code ASAP. michael.beckinsale wrote: Hi All, Is it possible to insert a VBA module (which has been exported to say My Documents) into an existing workbook (say myBook1)and then run the macro in myBook1. I would like to control the operation from a "Control" workbook so that l can select the source & target multiple times as l need to add this VBA module to over 50 existing workbooks. Any idea's / example code greatly appreciated Regards Michael beckinsale. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change the fx to = in the inset function bar | Setting up and Configuration of Excel | |||
Can't rename or inset a tab | Excel Discussion (Misc queries) | |||
how to inset date in excel 07 | New Users to Excel | |||
Cell Inset? | New Users to Excel | |||
Delete a row and inset a row - Lotus equivalent | Setting up and Configuration of Excel |