[Solved] excel vba compare & copy worksheets from 2 excel files

by


First of all you need to loop through all your worksheets for both, open the 2 workbooks, and loop through both, then you can select them, compare if their name exists in the other worksheet, if not, you add it, otherwise, you copy the content from one to the other one and at the end you should save them. Solution in C#, see link for solution in VB.NET, something similiar is to apply for VBA.

Excel.Workbook workbook;
workbook = xlsApp.Workbooks.Open("first file", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Workbook workbook2;
workbook2 = xlsApp.Workbooks.Open("second file", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
foreach (Excel.Worksheet sheet in workbook.Sheets)
{
    workbook.Activate();
    sheet.Activate();
    foreach (Excel.Worksheet sheet2 in workbook2.Sheets)
    {
         workbook2.Activate();
         sheet2.Activate();
         if(sheet2.Name.Equals(sheet.Name))
         {
             sheet.Range("A1:Z65535").Copy(Missing.Value);
             sheet2.Range(cellmapp).PasteSpecial(); // where cellmap = A1:Z65535
         }
         else
         {
             Excel.Worksheet newWorksheet;
             newWorksheet = (Excel.Worksheet)this.Application.Worksheets.Add();
             sheet.Range("A1:Z65535").Copy(Missing.Value);
             newWorksheet.Range(cellmapp).PasteSpecial(); // where cellmap = A1:Z65535
             snewWorksheet.Range("A1:Z65535").Copy(Missing.Value);
             newWorksheet.Columns.AutoFit();
         }
    }
}

http://www.developerfusion.com/tools/convert/csharp-to-vb/?batchId=36c4611b-294a-4f87-a762-1016ad46d508

1

solved excel vba compare & copy worksheets from 2 excel files