Copying macro from one workbook to another workbook

Aspose.Cells Product Family
1

Hi I’m currently trying to copy a macro from one workbook to another workbook.

For example lets say I have…

  1. Workbook A, with Worksheets named “Worksheet1” and “Worksheet2” each having VBA code (Macros)

and I have

  1. Workbook B, with Worksheets named “Worksheet3” , “Worksheet1”, and “Worksheet2” with no VBA code (Macros).

I want to be able to copy the Macros from “Worksheet1” and “Worksheet2” from Workbook A and copy them to corresponding “Worksheet1” and “Worksheet2” in Workbook B.

Currently when I try to grab the name of the worksheet that the VBAModule is associated with using …

var outputPath = @"C:\Users\AC\Desktop\xlsm\test.xlsm";
FileStream newFstream = new FileStream(newPath, FileMode.Open);
var workbook = new Workbook(fstream);
VbaModuleCollection modules = workbook.VbaProject.Modules;
for (int i = 0; i < modules.Count; i++)
    {  Console.WriteLine(modules[i].**strong text**Name);
     }

Instead of the names “Worksheet1” and “Worksheet2”, I get Sheet1 and Sheet2 which seem to be based on when the worksheets were added to the workbook.

Is there a better way to identify which VBAModules are associated with which Worksheets rather then arbitrary names such as Sheet1, Sheet2, … Sheet10.

2

@austinc,
Please share your sample workbook “test.xlsm” for our testing. We will reproduce the scenario and share our feedback accordingly.

3

WorkbookA.zip (17.2 KB)
WorkbookB.zip (6.9 KB)

Thanks for the quick response. I’ve attached the two workbooks where I want to copy from WorkbookA to WorkbookB.

4

@austinc,
There is no direct way to get this information however you may try the following sample code which shows link between the module name and worksheet name. Please give it a try and share the feedback:

var workbook = new Workbook(@"WorkbookA.xlsm");
VbaModuleCollection modules = workbook.VbaProject.Modules;
for (int i = 0; i < modules.Count; i++)
{
    var name = (from ws in workbook.Worksheets where ws.CodeName == modules[i].Name select ws.Name).FirstOrDefault();
    Console.WriteLine($"Module Name:{modules[i].Name}, Worksheet name:{name}");
}