How do I merge multiple excel files to a single excel file

0 votes

Therefore, I was making an Excel sheet aggregator. In my line of business, we occasionally receive a large number of separate Excel files with only one sheet each that are all related.

I partially adopted the concepts from the prior post. But after doing so, I noticed that several of the duplicated excel sheets were empty. only some of them. Why some are blank and others are fine is beyond my comprehension.

The code I employ to access and copy Excel files is shown below.

         OpenFileDialog browse = new OpenFileDialog();
            browse.Multiselect = true;
            DialogResult result = browse.ShowDialog();

            if (result == DialogResult.OK)

                try //try to open it. If its a proper excel file
                {   
                    excel = new Excel.Application();
                    excel.Workbooks.Add("");
                    finalized = excel.Workbooks[1];
                    excel.SheetsInNewWorkbook = 1;
                    for(int i=0; i< browse.FileNames.Length; i++)
                    {
                        excel.Workbooks.Add(browse.FileNames[i]);
                    }
                    //skip the first workbook as it is the finalized one
                    //also note everything in excel starts at 1 and not 0
                    for(int i=2; i<excel.Workbooks.Count; i++)
                    {
                        int count = excel.Workbooks[i].Worksheets.Count;
                        excel.Workbooks[i].Activate();
                        for (int j = 1; j < count; j++)
                        {

                            Excel._Worksheet pastee = (Excel._Worksheet)excel.Workbooks[i].Worksheets[j];
                            Excel._Worksheet sheet = (Excel._Worksheet)finalized.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                            //Excel._Worksheet sheet = finalized.Sheets[1];
                            pastee.Copy(Before: sheet);


                        }//end of for j
                    }//end of for i
                }//end of try

Here is the code I use to save the excel file

            SaveFileDialog browse = new SaveFileDialog();
            browse.Title = "Save as Excel";
            browse.Filter = "Excel workbook | *.xlsx";
            DialogResult result = browse.ShowDialog();

            finalized.SaveAs(browse.FileName, Excel.XlFileFormat.xlWorkbookDefault);

            MessageBox.Show("Success", "Message");
            //unlock the file
            Global.releaseComObjects(finalized, excel);
Dec 24, 2022 in Others by Kithuzzz
• 38,010 points
573 views

1 answer to this question.

0 votes

You copy a worksheet from before each source sheet and add a new worksheet to your "finalised" workbook (the "sheet") in your inner loop. Because you actually create two sheets for each source sheet, every "sheet" created by your Add command will be empty. Another issue is that, as you pointed out, Excel's arrays are 1-based; hence, you must loop until j >= count rather than j count.

So I think that code would work better:

Excel.Worksheet dummy = finalized.Worksheets[1];

for (int i = 2; i <= excel.Workbooks.Count; i++)
{
    int count = excel.Workbooks[i].Worksheets.Count;

    for (int j = 1; j <= count; j++)
    {
        Excel._Worksheet pastee = (Excel._Worksheet)excel.Workbooks[i].Worksheets[j];
        pastee.Copy(dummy);
    }
}

dummy.Delete();
answered Dec 24, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How do I protect all worksheet in an Excel workbook with a single click?

VBA Code : Dim ws as Worksheet Dim pwd ...READ MORE

answered Oct 22, 2022 in Others by narikkadan
• 63,420 points
514 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,420 points
508 views
0 votes
1 answer

How do I transpose a column to a matrix in Excel?

With data in column A, pick some cells ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,420 points
1,489 views
0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,420 points
1,790 views
0 votes
1 answer

c#, Microsoft Interop Excel , change font style for s selected range

Change your code to this: worksheet.get_Range("B3", "B4").Cells.Font.Name = ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 63,420 points
1,342 views
0 votes
0 answers

Print Excel Worksheet/Workbook

I'm attempting to make a C# Winform ...READ MORE

Oct 31, 2022 in Others by Kithuzzz
• 38,010 points
488 views
0 votes
1 answer

Generate a flat list of all excel cell formulas

Hello, you'll have to follow certain steps ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
347 views
0 votes
1 answer

Convert a number to a letter in C# for use in Microsoft Excel [duplicate]

If you are familiar with using formulas ...READ MORE

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
571 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,420 points
1,546 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP