how to merge multiple excel files into one

Question

I have tried searching this on google but could get the answer for my problem. I am trying to merge multiple excel files into single one using below code. But it is giving me error like below (no additional info in innerexception) at sheet.Copy command.

Unable to get the Copy property of the Worksheet class

Code...

private void MergeXlsxFiles(string destXlsxFileName, params string[] sourceXlsxFileNames)
        {
            Application excelApp = null;
            Workbook destWorkBook = null;
            var temppathForTarget = Path.Combine(Directory.GetCurrentDirectory() , Guid.NewGuid() + ".xls");

            if (File.Exists(temppathForTarget))
                File.Delete(temppathForTarget);

            try
            {
                excelApp = new Application
                {
                    DisplayAlerts = false,
                    SheetsInNewWorkbook = 3
                };
                destWorkBook = excelApp.Workbooks.Add();
                destWorkBook.SaveAs(temppathForTarget);


                foreach (var sourceXlsxFile in sourceXlsxFileNames)
                {
                    var file = Path.Combine(Directory.GetCurrentDirectory(), sourceXlsxFile);
                    var sourceWorkBook = excelApp.Workbooks.Open(file);

                    foreach (Worksheet ws in sourceWorkBook.Worksheets)
                    {
                        var wSheet = destWorkBook.Worksheets[destWorkBook.Worksheets.Count];
                        ws.Copy(wSheet);
                        destWorkBook.Worksheets[destWorkBook.Worksheets.Count].Name =
                            ws.Name; 
                    }
                    sourceWorkBook.Close(XlSaveAction.xlDoNotSaveChanges);
                }
                destWorkBook.Sheets[1].Delete();
                destWorkBook.SaveAs(destXlsxFileName);
            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (destWorkBook != null)
                    destWorkBook.Close(XlSaveAction.xlSaveChanges);
                if (excelApp != null)
                    excelApp.Quit();
            }
        }

Does anyone knows what is wrong with this code.

I am referring Microsoft.Office.Interop.Excel dll from GAC with version 15.0.0.0 and I have MSOffice 2013 installed on my machine.

Possibly due to in sourceWorkBook.Sheets and/or destWorkBook.Sheets. Sheets are not the same things as Worksheets. They can be, but they can also be other kinds of sheets, such as chart sheets. Try in sourceWorkbook.Worksheets and destWorkBook.Worksheets`? — Cindy Meister
– Cindy Meister, Commented Dec 21, 2018 at 12:19
I tried with Worksheets but still the same error. I updated my question with these changes... — Abhash786
– Abhash786, Commented Dec 24, 2018 at 4:45

Cindy Meister · Accepted Answer · 2018-12-24 16:35:58Z

1

In general, when I test the code in the question, it runs into problems with data typing. Excel is "picky" about data typing - it's often necessary to explicitly cast a type. The following foreach works for me: Notice the (Excel.Worksheet) casts. With those I ran into no problems with the Copy (or the Name) method (property).

I also ran into "oddities" assigning sheet names. The logic used in the code in the question is not clear, so on the assumption the new sheets should be added after the default three empty sheets, I altered ws.Copy to put them at the end.

foreach (Excel.Worksheet ws in sourceWorkBook.Worksheets)
{
    var wSheet = (Excel.Worksheet) destWorkBook.Worksheets[destWorkBook.Worksheets.Count];
    ws.Copy(missing, wSheet);
    Excel.Worksheet wNewSheet = (Excel.Worksheet)destWorkBook.Worksheets[destWorkBook.Worksheets.Count];
    wNewSheet.Name = "New" + ws.Name; 
}

answered Dec 24, 2018 at 16:35

Cindy Meister

25.8k21 gold badges36 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Abhash786 Over a year ago

That works for me. Thanks a lot... One more thing.... My old code also work when my excel files extension is .xlsx not with .xls

Cindy Meister Over a year ago

When using the "interop" - the code is interacting with the Excel application - the file format/extension doesn't matter. Excel interacts with all file types it can open the same way. What matters with the "interop" is the version of Excel - that determines what functionality is available. If code is working with the Open XML file format directly (Open XML SDK, for instance) then the file format makes a difference.

Collectives™ on Stack Overflow

how to merge multiple excel files into one

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related