none
How can I copy values in a range in one workbook, then set the cells in all workbooks in contained in subfolders to those values? RRS feed

  • Question

  • Hello. I'm using office 365 64 bit and VS2017 community.

    I'm trying to copy values from the first sheet in one workbook, to the first sheet in all other workbooks contained in all the sub-directories.

    I've got this code so far but I'm getting error message System.Runtime.InteropServices.COMException: 'Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))'

    Can anyone help me get this to work? Am I even close? : (

    private void button1_Click(object sender, EventArgs e)
            {
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
                Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;
                xlWorkBook = xlApp.Workbooks.Add(misValue);
    
                Excel.Workbook wb = xlApp.Workbooks.Open(@"C:\Users\Sniffles\Desktop\JTools\Lesson_Plans\Lesson_Plan_Date_Changer.xlsm");
                xlApp.ScreenUpdating = false;
                xlApp.Visible = false;
    
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    
                //Get Lesson_Plan Date_Changer Updated Date Values
                var lpData = xlWorkSheet.get_Range("A2", "C50").Value2;
    
                //Apply Lesson_Plan Date_Changer Updated Date Values to all workbooks in these directories
                foreach (var file in Directory.EnumerateFiles((@"C:\Users\Sniffles\Desktop\JTools\Lesson_Plans\Quarter_1"), "*.xlsm", SearchOption.AllDirectories))
                {
                    xlWorkSheet.Cells["a2", "c50"] = lpData;
                }
    
                foreach (var file in Directory.EnumerateFiles((@"C:\Users\Sniffles\Desktop\JTools\Lesson_Plans\Quarter_2"), "*.xlsm", SearchOption.AllDirectories))
                {
                    xlWorkSheet.Cells["a2", "c50"] = lpData;
                }
                foreach (var file in Directory.EnumerateFiles((@"C:\Users\Sniffles\Desktop\JTools\Lesson_Plans\Quarter_3"), "*.xlsm", SearchOption.AllDirectories))
                {
                    xlWorkSheet.Cells["a2", "c50"] = lpData;
                }
    
                foreach (var file in Directory.EnumerateFiles((@"C:\Users\Sniffles\Desktop\JTools\Lesson_Plans\Quarter_4"), "*.xlsm", SearchOption.AllDirectories))
                {
                    xlWorkSheet.Cells["a2", "c50"] = lpData;
                };
            }

    Sunday, July 22, 2018 4:53 PM

Answers

  • Hello _sniffles_,

    You could not got a range of cells in the format Cells["A1","A2"]. If you want to get the range, use the get_Range of Range property.

    Besides, your IpData is got from xlWorkBook which is a newly created workbook and there is no data in it.

    I think your code should maybe something like.

    Excel.Workbook wb = xlApp.Workbooks.Open(@"C:\Users\terryx\Desktop\TestFolder\Book1.xlsm");
                xlApp.ScreenUpdating = false;
                xlApp.Visible = true;
    
                
                var lpData= wb.Worksheets[1].Range["A4:D13"].Value2;
    
                foreach (var file in Directory.EnumerateFiles((@"C:\Users\terryx\Desktop\TestFolder\Test_Folder"), "*.xlsx", SearchOption.AllDirectories))
                {
                    xlWorkBook = xlApp.Workbooks.Open(file);
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                    xlWorkSheet.get_Range("A4", "D13").Value2 = lpData;
                    xlWorkBook.Close(true);
                }

    Best Regards,

    Terry


    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.

    • Marked as answer by _Sniffles_ Monday, July 23, 2018 3:03 AM
    Monday, July 23, 2018 1:39 AM

All replies

  • Hello _sniffles_,

    You could not got a range of cells in the format Cells["A1","A2"]. If you want to get the range, use the get_Range of Range property.

    Besides, your IpData is got from xlWorkBook which is a newly created workbook and there is no data in it.

    I think your code should maybe something like.

    Excel.Workbook wb = xlApp.Workbooks.Open(@"C:\Users\terryx\Desktop\TestFolder\Book1.xlsm");
                xlApp.ScreenUpdating = false;
                xlApp.Visible = true;
    
                
                var lpData= wb.Worksheets[1].Range["A4:D13"].Value2;
    
                foreach (var file in Directory.EnumerateFiles((@"C:\Users\terryx\Desktop\TestFolder\Test_Folder"), "*.xlsx", SearchOption.AllDirectories))
                {
                    xlWorkBook = xlApp.Workbooks.Open(file);
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                    xlWorkSheet.get_Range("A4", "D13").Value2 = lpData;
                    xlWorkBook.Close(true);
                }

    Best Regards,

    Terry


    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.

    • Marked as answer by _Sniffles_ Monday, July 23, 2018 3:03 AM
    Monday, July 23, 2018 1:39 AM
  • Thank you so much Terry, you saved me once again. I can see where I made a lot of mistakes. This works perfectly. Thank you again.
    Monday, July 23, 2018 3:02 AM