Copy/paste data from several Excel files in a folder into one single Excel file

EDN Admin

EDN Admin

Well-known member
Joined
Aug 7, 2010
Messages
12,794
Location
In the Machine
<span style="font-family:Times New Roman,serif I have some VB code that does a real nice job of copying/pasting data from several Excel files in a folder into one single Excel file in the same folder.<span>
Again, it works fine in VB, but when I used one of those online tools to convert the code, some things got scrabbled, so I defer to the C# experts who live here.<span>
Here is the code:
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue using<span style="font-family:Times New Roman,serif System;
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue using<span style="font-family:Times New Roman,serif System.Collections.Generic;
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue using<span style="font-family:Times New Roman,serif System.ComponentModel;
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue using<span style="font-family:Times New Roman,serif System.Data;
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue using<span style="font-family:Times New Roman,serif System.Drawing;
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue using<span style="font-family:Times New Roman,serif System.Linq;
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue using<span style="font-family:Times New Roman,serif System.Text;
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue using<span style="font-family:Times New Roman,serif System.Windows.Forms;
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue using<span style="font-family:Times New Roman,serif Excel = Microsoft.Office.Interop.Excel;
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue using<span style="font-family:Times New Roman,serif Microsoft.Office.Interop.Excel;
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue using<span style="font-family:Times New Roman,serif System.IO;
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue namespace<span style="font-family:Times New Roman,serif WindowsFormsApplication2
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif {
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span style="color:blue public <span style="color:blue partial <span style="color:blue
class <span style="color:#2B91AF Form1 : <span style="color:#2B91AF
Form
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
{
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span style="color:blue public Form1()
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
{
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
InitializeComponent();
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
}
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span style="color:blue private <span style="color:blue void button1_Click(<span style="color:blue object sender,
<span style="color:#2B91AF EventArgs e)
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
{
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
Main();
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
}
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span> <span style="color:blue public <span style="color:blue
void Main()
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
{
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span style="color:blue string filePath = <span style="color:#A31515
"C:\Users\Excel\Desktop\Ryan_Folder\MainExcel.xls";
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
Microsoft.Office.Interop.Excel.<span style="color:#2B91AF Application xlobj =
<span style="color:blue new Microsoft.Office.Interop.Excel.<span style="color:#2B91AF Application();
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span style="color:#2B91AF Workbook w = <span style="color:blue default(<span style="color:#2B91AF Workbook);
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span style="color:#2B91AF Workbook w1 = <span style="color:blue
default(<span style="color:#2B91AF Workbook);
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span style="color:#2B91AF Worksheet s = <span style="color:blue
default(<span style="color:#2B91AF Worksheet);
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span style="color:#2B91AF Worksheet s1 = <span style="color:blue
default(<span style="color:#2B91AF Worksheet);
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span style="color:#2B91AF Worksheet xlsht = <span style="color:blue
default(<span style="color:#2B91AF Worksheet);
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span style="color:blue int intItem = 1;
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span style="color:#2B91AF DirectoryInfo dirSrc = <span style="color:blue
new <span style="color:#2B91AF DirectoryInfo(<span style="color:#A31515 "C:\Users\Excel\Desktop\Ryan_Folder\");
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span style="color:blue foreach (<span style="color:blue object ChildFile_loopVariable
<span style="color:blue in dirSrc.GetFiles()) {
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
ChildFile = ChildFile_loopVariable;
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span style="color:blue try {
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span style="color:green // Renaming the excel sheet
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
w = xlobj.Workbooks.Open(ChildFile.FullName);
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span>
w1 = xlobj.Workbooks.Open(filePath);
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span style="color:blue if (intItem > 3) {
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
xlsht = w1.Worksheets.Add;
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
}
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
s = w.Worksheets.Item(1);
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
s1 = w1.Worksheets.Item(intItem);
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
s1.Name = ChildFile.Name;
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span style="color:green // it will copy and paste sheet from one to another with formula
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
s.UsedRange.Copy();
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
s1.PasteSpecial();
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
s1.UsedRange.Formula = s.UsedRange.Formula;
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span style="color:green // Renaming the excel sheet
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
w.Save();
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
w1.Save();
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
w.Close();
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
w1.Close();
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
} <span style="color:blue catch (<span style="color:#2B91AF Exception ex) {
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
w.Save();
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
w1.Save();
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
w.Close();
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
w1.Close();
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
}
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
intItem = intItem + 1;
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
}
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span style="color:green //Dts.TaskResult = ScriptResults.Success
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
<span> }
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif <span>
}
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif }
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<span style="font-family:Times New Roman,serif Here is a list of errors that I’m seeing.
<span style="font-family:Times New Roman,serif <img alt="" src="http://social.msdn.microsoft.com/Forums/getfile/187614 <br type="_moz

<span style="font-family:Times New Roman,serif This is probably an easy fix.<span>
I don’t know C# well enough to know how to fix the errors. <hr class="sig Ryan Shuell

View the full article
 

Similar threads

C
Trouble with getting out Email adresses from email Body..
Replies
0
Views
164
Carlo Goretti
C
EDN Admin
Trying to update data in a SQL Server Table from a DataGrid
Replies
0
Views
92
EDN Admin
EDN Admin
EDN Admin
The parameterized query @Fname expects a parameter which was not supplied.
Replies
0
Views
125
EDN Admin
EDN Admin
EDN Admin
provider cannot be found. it may not be properly installed
Replies
0
Views
442
EDN Admin
EDN Admin
EDN Admin
Why would DataGrid preserves the original order/format?
Replies
0
Views
91
EDN Admin
EDN Admin
Back
Top