hi,
this code is not doing well but it is coping the data of only one xls and pasting the data of only one file. it is opening second xls in folder and coping but not pasting ...
so please help.....
string fileinputname = txtinput.Text;
string fileoutputname = txtoutput.Text;
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(txtinput.Text);
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);
//this doesn't make any sense
//w1 = xlobj.Workbooks.Open(filePath);
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.get_Range("A1", 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);
MessageBox.Show("data successfully copied");
}
catch (Exception ex)
{
w.Save();
w1.Save();
w.Close(false, Type.Missing, Type.Missing);
w1.Close(false, Type.Missing, Type.Missing);
}
intItem = intItem + 1;
}