Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 28
Default Is it possible to copy vba macros into a workbook using a vba macroto do the copying

I have a series of workbooks one for each year handling budgetary data that prepares tax information for the year in question. If I modify a macro or add a new macro, I have to manually make the modification in each of the workbooks (12 in all). Each workbook has links back to the preceding workbook so that cumulative data from year 1 can be presented in the current year.

I would like to make the VBA changes in the base year's workbook and then migrate that changed macro to all the other 11 workbooks using VBA code. Is this possible ?.

I am already using PERSONAL.XLSM to hold a library of commonly used macros.. However, I think it is poor design to place macros common to one particular series of workbooks in PERSONAL.XLSM.

Looking forward to being enlightened. Thank you.
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Is it possible to copy vba macros into a workbook using a vba macro to do the copying

You could put the macros in an XLAM (addin) that also loads a custom
menu. This makes the current code always contained in a single
(distributable) file, and obviates the need for your period files to
contain any code at all. Just redistribute any new version of your
addin as needed!

Normally, I'd 'tag' the project workbooks so the addin knows which
files to make its menus available to. I use a template for the fiscal
period file so a new one can be started in new fiscal periods.

So then...

Addin:
- contains all code/forms/features/functionality
- creates/destroys its own custom menus at startup/shutdown
- only enables menus when one of its project files is active

Template:
- reusable project file used by the addin for dedicated tasks
- new template created by the addin for each new fiscal period

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 28
Default Is it possible to copy vba macros into a workbook using a vbamacro to do the copying

On Sunday, April 10, 2016 at 1:02:14 PM UTC-4, Desmond Walsh wrote:
I have a series of workbooks one for each year handling budgetary data that prepares tax information for the year in question. If I modify a macro or add a new macro, I have to manually make the modification in each of the workbooks (12 in all). Each workbook has links back to the preceding workbook so that cumulative data from year 1 can be presented in the current year.

I would like to make the VBA changes in the base year's workbook and then migrate that changed macro to all the other 11 workbooks using VBA code. Is this possible ?.

I am already using PERSONAL.XLSM to hold a library of commonly used macros. However, I think it is poor design to place macros common to one particular series of workbooks in PERSONAL.XLSM.

Looking forward to being enlightened. Thank you.


Actually, I found a VBA macro CopyModule on Chip Pearson's webite www.cpearson.com that does exactly what I want. The following test code shows how to use this macro.

Sub test_CopyModule()
'
' A test procedure to test the Vba macro CopyModule
' NOTE 1: CopyModule is from Chip Pearson's site www.cpearson.com
' (check topic VBA EDitor, Automating The VBA EDitor and its objects)
' NOTE 2: The module may be any VBComponent such as ThisWorkbook, ModuleN,
' SheetN or any form in Forms
' Updated: 10-Apr-2016
'---------------------------------------------------------------------------
Dim result As Boolean
Dim FromVBProject As VBIDE.VBProject
Dim ToVBProject As VBIDE.VBProject
Dim from_wb As Workbook
Dim to_wb As Workbook

Set from_wb = Workbooks("Tax_Section216_2005.xlsm")
Set to_wb = Workbooks("test_copy_macro.xlsm")

Set FromVBProject = from_wb.VBProject
Set ToVBProject = to_wb.VBProject

result = CopyModule("Module1", FromVBProject, ToVBProject, True)
End Sub
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Is it possible to copy vba macros into a workbook using a vba macro to do the copying

This will copy the entire module, resulting in duplicate procedure
names if you don't remove the existing module first. Not to mention
having to do maintenance on every workbook, wouldn't it be easier (and
more productive/efficient) to maintain 1 addin?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 28
Default Is it possible to copy vba macros into a workbook using a vbamacro to do the copying

On Monday, April 11, 2016 at 9:38:40 AM UTC-4, GS wrote:
This will copy the entire module, resulting in duplicate procedure
names if you don't remove the existing module first. Not to mention
having to do maintenance on every workbook, wouldn't it be easier (and
more productive/efficient) to maintain 1 addin?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Yes, your point is very valid. I will look into the addin technique that you suggested and report if I have any difficulty. Thank you for taking the time to reply

Desmond
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Is it possible to copy vba macros into a workbook using a vba macro to do the copying

On Monday, April 11, 2016 at 9:38:40 AM UTC-4, GS wrote:
This will copy the entire module, resulting in duplicate procedure
names if you don't remove the existing module first. Not to mention
having to do maintenance on every workbook, wouldn't it be easier
(and more productive/efficient) to maintain 1 addin?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Yes, your point is very valid. I will look into the addin technique
that you suggested and report if I have any difficulty. Thank you for
taking the time to reply

Desmond


If you do a lot of this sort of work, addins would be of great benefit.
For reference material you could check out the following books...

http://www.appspro.com/Books/Books.htm

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
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
Macroto delete extra space in a cell Metfan Excel Programming 3 January 17th 13 04:04 AM
Copying macros to another workbook dpatcep Excel Discussion (Misc queries) 2 September 9th 09 09:03 PM
Use VBA to copy macros from one workbook to another Dean@ERYC[_2_] Excel Programming 3 April 30th 08 04:19 PM
Copy workbook without Macros Robert[_34_] Excel Programming 1 February 15th 07 02:44 AM
loop through a column on a workbook copying data on each row to another workbook, then copy data back to the original workbook burl_rfc Excel Programming 1 April 1st 06 08:48 PM


All times are GMT +1. The time now is 10:16 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"