如何将工作表从多个工作簿合并到新工作簿中 [英] How to merge Worksheets from Multiple Workbooks into New WorkBook

问题描述

我想合并来自多个工作簿的多个工作表的数据.例如，我有多个带有多个工作表的工作簿.

I want to merge data from multiple worksheets of multiple workbooks. For example, I have multiple Workbooks with multiple worksheets.

因此，请考虑我要合并"WorkBook1"的"Worksheet1"中的数据和"Workbook2"的"Worksheet2"中的数据，并分别合并到新"WorkBook"的"NewWorkSheet"中.我想这样做的方式是，将数据插入到另一个下面的"NewWorkSheet"中，例如"Worksheet1"中的前20行，然后接下来是"Worksheet2"中的20个，依此类推...

So consider I want to merge data from "Worksheet1" of "WorkBook1" and data from "Worksheet2" of "Workbook2" and respectively into "NewWorkSheet" of New "WorkBook". I want to do this in a way that data is inserted into "NewWorkSheet" one below the other, like the first 20 rows from "Worksheet1" then next 20 from "Worksheet2" and so on...

我只能从一个工作表合并到一个新工作表，而不能从其他工作表合并到新工作表.

I could merge into a new one only from one worksheet only but not from others.

Workbook xlWorkbookDLA3 = xlApp.Workbooks.Open(dropBoxPath + "Week49DLA3.xlsm");
Worksheet worksheetDLA3 = xlWorkbookDLA3.Worksheets["Wed-A"];

Workbook xlWorkbookDLA4 = xlApp.Workbooks.Open(dropBoxPath + "Week49DLA4.xlsm");
Worksheet worksheetDLA4 = xlWorkbookDLA4.Worksheets["Thurs-A"];

Workbook destionationworkBook = xlApp.Workbooks.Open(dropBoxPath + "Test.xlsm");
Worksheet destworkSheet = destionationworkBook.Worksheets["Sheet2"];

Microsoft.Office.Interop.Excel.Range from, to;


from = worksheetDLA3.Range["B7:S7"].EntireColumn;
to = destworkSheet.Range["B7:S7"].EntireColumn;

from.Copy(to);


destionationworkBook.SaveAs(@"C:\Users\ashish.mokadam\Desktop\DropBox\Test" + ".xlsm");

推荐答案

对于每个后续工作表，您都希望从中复制 (请注意，第一个工作表为 NOT )，在"from.Copy(to);"之间插入以下代码示例和"destionationworkBook.SaveAs(...":

For each subsequent worksheet you wish to copy from (note, NOT for the 1st worksheet), insert the following code sample between "from.Copy(to);" and "destionationworkBook.SaveAs(...":

     var destination_start_row = destworkSheet.UsedRange.Rows.Count + 1;  // + 1 because Excel is 1-indexed
     var source_used_rows      = worksheetDLA4.UsedRange.Rows.Count;

     from = worksheetDLA4.Range["B1:S" + source_used_rows];
     to   = destworkSheet.Range["B" + destination_start_row + ":S" + destination_start_row + source_used_rows];
     from.Copy(to);

问题是您试图将整个列从源工作表复制到目标工作表，从而覆盖目标工作表中的值.该代码仅复制一定范围的单元格，而不复制整个数据列.

The problem is that you are trying to copy entire columns from the source worksheets to the destination worksheet, thus overwriting the values in the destination worksheet. This code only copies a range of cells, not entire columns of data.

此外，您的变量名"desti o nationworkBook"中有一个错字.它应该是"destinationworkBook".

Also, you have a typo in your variable name "destionationworkBook". It should be "destinationworkBook".

这篇关于如何将工作表从多个工作簿合并到新工作簿中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！