Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
|
|||
|
|||
Mời bạn đọc bài viết về bất động sản
Choáng ngợp những pḥng khách sạn đắt đỏ nhất thế giới Cơ ngơi sang trọng của bà chủ công ty người mẫu Venus Soi ngôi nhà ngốn núi tiền của ca sĩ Trang Nhung |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macroto delete extra space in a cell | Excel Programming | |||
Copying macros to another workbook | Excel Discussion (Misc queries) | |||
Use VBA to copy macros from one workbook to another | Excel Programming | |||
Copy workbook without Macros | Excel Programming | |||
loop through a column on a workbook copying data on each row to another workbook, then copy data back to the original workbook | Excel Programming |