none
I am trying to merge all Excel files in a folder into a Master Excel file. RRS feed

  • Question

  • I'm testing this code and I think it's pretty darn close, but something is off here.  I believe it is the range in the s1 file.  I think the problem lies here.

    Excel.Range r = s1.get_Range(s1.UsedRange.Row + 1, Type.Missing);

    Or, it may be like this.

    Excel.Range r = (s1.UsedRange.Row + s1.UsedRange.Rows.Count - 1);

    I tried both and neither worked for me.

    Here is my sample code.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    using Microsoft.Office.Interop.Excel;
    using System.IO;
    
    namespace WindowsFormsApplication3
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                Main();
            }
    
            public void Main()
            {                     
                string filePath = "C:\\Users\\Excel\\Desktop\\excel_files\\MainExcel.xlsx";
                Microsoft.Office.Interop.Excel.Application xlobj = new Microsoft.Office.Interop.Excel.Application();
                Workbook w = default(Workbook);
                Workbook w1 = default(Workbook);
                Worksheet s = default(Worksheet);
                Worksheet s1 = default(Worksheet);
                Worksheet xlsht = default(Worksheet);
                int intItem = 1;                                                   
                DirectoryInfo dirSrc = new DirectoryInfo(@"C:\Users\Excel\Desktop\excel_files\");
                foreach (FileInfo ChildFile in dirSrc.GetFiles())
                {
                    try
                    {
                        // Renaming the excel sheet
                        w = xlobj.Workbooks._Open(ChildFile.FullName, 
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing);
                        
                        w1 = xlobj.Workbooks.Open(filePath);
                        xlobj.Visible = true;
    
                        w1 = xlobj.Workbooks._Open(filePath,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing);
    
                        if (intItem > 3)
                        {
                            Excel.Worksheet lastSht =
                                 (Excel.Worksheet)w1.Worksheets[w1.Worksheets.Count];
                            xlsht = (Excel.Worksheet)w1.Worksheets.Add(Type.Missing,
                                lastSht, 
                                Type.Missing, Type.Missing);
                        }
                        s = (Excel.Worksheet)w.Worksheets[1];
                        s1 = (Excel.Worksheet)w1.Worksheets[intItem];
                        s1.Name = ChildFile.Name;
    
                        // it will copy and paste sheet from one to another with formula
                        s.UsedRange.Copy(Type.Missing);
    
    
                        //Excel.Range r = (s1.UsedRange.Row + s1.UsedRange.Rows.Count - 1);
                        Excel.Range r = s1.get_Range(s1.UsedRange.Row + 1, Type.Missing);
                        r.PasteSpecial(Excel.XlPasteType.xlPasteValues,
                              Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
                              Type.Missing, Type.Missing);
                        s1.UsedRange.Formula = s.UsedRange.Formula;
    
                        // Renaming the excel sheet
                        //w.Save();
                        w1.Save();
                        w.Close(false, Type.Missing, Type.Missing);
                        w1.Close(false, Type.Missing, Type.Missing);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.ToString());
                        //w.Save() = false;
                        w1.Save();
                        w.Close(false, Type.Missing, Type.Missing);
                        w1.Close(false, Type.Missing, Type.Missing);
                    }
                    intItem = intItem + 1;
                }
                
            }
        }
    }
    
    




    MY BOOK

    Monday, January 16, 2017 7:22 PM

All replies

  • Hi,

    Please test if the following code works for you.

            Excel.Range r = s1.Cells[s1.UsedRange.Rows.Count + 1, 1];

    If we want to use get_Range to get the cell and then paste, we need to specify the same size like copied range.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, January 17, 2017 3:23 AM
    Moderator
  • Hummm, I thought that would work based on your explanation.  It only worked for the first file; when I got to the second I got this error message.

    Any thoughts on what to trey next?


    MY BOOK


    • Edited by ryguy72 Tuesday, January 17, 2017 4:30 AM
    Tuesday, January 17, 2017 4:30 AM
  • Hi,

    You get this error because the condition: if (intItem > 3). There is no sheets(2) in your MainExcel workbook when copying the 2nd workbook.

    I had no idea if there was at least three sheets in your MainExcel workbook.

    Now I think your MainExcel workbook is a new workbook to collect all the data. So just remove the if condition and I think there is no need to use usedrange.rows.count+1 to get the range. Because for a new worksheet, although the sheet is blank, usedrange.rows.count would be one. So after copying, the row 1 would be empty. I suggest you specify the range into Excel.Range r = s1.Cells[1, 1];

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 17, 2017 5:52 AM
    Moderator
  • Yeah, that was it.  Thanks.

    MY BOOK

    Wednesday, January 18, 2017 12:44 AM
  • I want to do the same in VBA, its possible?
    Wednesday, January 18, 2017 12:56 AM
  • I want to do the same in VBA, its possible?

    Im using excel 2016 for Mac... but I want to use in both: Mac and Windows... Integrated to other routine that I wrote

    Thanks

    Wednesday, January 18, 2017 12:59 AM
  • Yeah, that was it.  Thanks.

    MY BOOK

    Hi, ryguy72

    If the original issue has been resolved, I suggest you mark helpful post as answer or you could share your complete solution here.

    Thanks for your understanding.

    Regards,

    Celeste



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 18, 2017 6:49 AM
    Moderator
  • I want to do the same in VBA, its possible?

    Im using excel 2016 for Mac... but I want to use in both: Mac and Windows... Integrated to other routine that I wrote

    Thanks


    Hi, wised

    You could do it using VBA.

    AFAIK, there might be compatible issue between VBA for Mac and Windows.

    This forum is for windows platform, so if you have any issue about developing with Office object model in Windows, please free feel to post your question.

    If you have any issue related to Office for Mac, I suggest you post on  https://answers.microsoft.com/en-us/msoffice. The supporters there would offer better help for you.

    Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 18, 2017 6:50 AM
    Moderator