Copying macro in c# 1

[christhedonstar]

Hi,

Is there a way to copy the macros from one workbook into another using c#?

Thanks,

Chris

 

[christhedonstar]

I'm talking about Excel modules (that contain macros).

 

[bfair123]

You should be able to do this by utilizing the Excel COM object model. You'll need to add a reference to Microsoft Office Excel in your project (on the COM tab, mine is "Microsoft Excel 11.0 Object Library"). The code below should be close to what you need:

using Microsoft.Vbe.Interop;
using System.Reflection;
using System.Runtime.InteropServices;

Excel.Application app = new Excel.ApplicationClass();
app.Visible = false;
string path1 = @"C:\oldModules.xls", path2 = @"C:\newModules.xls";
// open up the source and destination spreadsheets
Excel._Workbook wbSource = app.Workbooks.Open(path1, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value),
wbDestination = app.Workbooks.Open(path2, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
VBComponent dest = null, current = null;
// Iterate existing workbook and copy over the code modules
IEnumerator e = wbSource.VBProject.VBComponents.GetEnumerator();
e.Reset();
while(e.MoveNext())
{
	current = (VBComponent)e.Current;
	dest = wbDestination.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
	dest.CodeModule.AddFromString(current.CodeModule.get_Lines(1, current.CodeModule.CountOfLines));
	dest.Name = current.Name;
	wbDestination.Save();
	Marshal.ReleaseComObject(dest);
	Marshal.ReleaseComObject(current);
	dest = null;
	current = null;
}
e = null;
// save and close the updated workbook
wbDestination.Close(true, Missing.Value, Missing.Value);
app.Quit();
// release com objects
Marshal.ReleaseComObject(wbSource);
Marshal.ReleaseComObject(wbDestination);
Marshal.ReleaseComObject(app);

 

[christhedonstar]

Thanks - It throws an exception on dest.CodeModule.AddFromString(current.CodeModule.get_Lines(1, current.CodeModule.CountOfLines))

and current.name is thisworkbook so it looks like it is picking up the excel objects rather than the modules...

 

[christhedonstar]

It looks like I can use this code to get back a real code module
current.Collection.Item(4).CodeModule.Name but I'm not sure how to differentiate between a codemodule and worksheet or the workbook. They all return the type of codemodule...

Did the code you posted work for you?

 

[christhedonstar]

Ignore my previous comments regarding the module...

However if you could let me know whether the code worked for you, that would be good. Thanks, Chris

 

[christhedonstar]

I think I just need to work through it properly... don't worry about replying... The code assumes there is code in every code module and probably some other stuff...

I'll modify it and post it back when done...

Thanks a lot for a really good starting point.

 

[christhedonstar]

Here is the updated code... I've taken out the workbook object creation:

public Boolean copyMacros()
{
VBComponent dest;
// Iterate existing workbook and copy over the code modules

try
{
foreach (VBComponent source in myWorkBookSource.VBProject.VBComponents)
{
//Do we have any code lines in the code module to copy?
if (source.CodeModule.CountOfLines > 0)
{
dest = myWorkBookDestination.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
dest.CodeModule.AddFromString(source.CodeModule.get_Lines(1, source.CodeModule.CountOfLines));
dest.Name = source.Name;
//wbDestination.Save();
Marshal.ReleaseComObject(dest);
Marshal.ReleaseComObject(source);
dest = null;
}

}
}

Is there a specific reason why do you get the enumerator rather than use a foreach statement in your code?

Thanks,

Chris

 

[christhedonstar]

Hmm still doesn't work for code in sheets and workbooks... I'll revise... Enumerator question still stands.

 

[christhedonstar]

Final code below:

public Boolean copyMacros()
{
VBComponent dest;
Boolean found;
// Iterate existing workbook and copy over the code modules
found = false;
try
{
foreach (VBComponent source in myWorkBookSource.VBProject.VBComponents)
{
//Do we have any code lines in the code module to copy?
if (source.CodeModule.CountOfLines > 0)
{


//We need to check whether we already have a code module with that name in our workbook
//This will be for the sheets and workbook and we assume that we have already copied the sheets accordingly
foreach (VBComponent destNew in myWorkBookDestination.VBProject.VBComponents)
{
if (destNew.Name == source.CodeModule.Name)
{
destNew.CodeModule.InsertLines(1, source.CodeModule.get_Lines(1, source.CodeModule.CountOfLines));
found = true;
break; //We've found the matching codemodule so lets exit
}
else //we have to create the code module
{
found = false; //set found to false so we can add the codemodule.
}
}
if (found == false)
{
dest = myWorkBookDestination.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
dest.CodeModule.AddFromString(source.CodeModule.get_Lines(1, source.CodeModule.CountOfLines));
//dest.Collection.Item(1).Name;
dest.Name = source.Name;
Marshal.ReleaseComObject(dest);
Marshal.ReleaseComObject(source);
dest = null;
}
}

}
}