I'm trying to copy a range of cells from one workBook to another, it works fine but when I try to use the SaveAs Function with transpose, it gives me an error:
System.Reflection.TargetInvocationException: 'Exception has been thrown by the target of an invocation.'
Inner Exception. COMException: PasteSpecial method of Range class failed
Here's my code:
private void CopyTranspose(string rawFile, string tempFile, string dataFile, string oldCells, string newCells)
{
try
{
object missing = Type.Missing;
//Copying data
Excel.Application oldApp = new Excel.Application();
oldApp.Application.DisplayAlerts = false;
oldApp.Application.Visible = false;
oldApp.Application.DisplayAlerts = false;
Excel.Workbook oldWorkBook = oldApp.Workbooks.Open(rawFile);
Excel.Worksheet oldWorkSheet = oldWorkBook.Worksheets.get_Item(1);
Excel.Range oldRange = oldWorkSheet.Range[oldCells];
oldRange.Copy(Type.Missing);
//Pasting data
Excel.Application newApp = new Excel.Application();
Excel.Workbook newWorkBook = newApp.Workbooks.Open(tempFile, 0, false);
newApp.Application.DisplayAlerts = false;
newApp.Application.Visible = false;
newApp.Application.DisplayAlerts = false;
Excel.Worksheet newWorkSheet = newWorkBook.Worksheets.get_Item(1);
Excel.Range newRange = newWorkSheet.Range[newCells];
newRange.Select();
newWorkSheet.Paste(Type.Missing, Type.Missing); //This line works but it doesn't do the transpose
newRange.PasteSpecial(Excel.XlPasteType.xlPasteValues,
Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, true, true); //this line does the transpose but it fails
oldWorkBook.Close(false, null, null);
oldApp.Quit();
//Saving File
newWorkBook.SaveAs(dataFile, missing, missing, missing, missing, false);
newWorkBook.Close(false, null, null);
newApp.Quit();
}
catch (Exception ex) { Console.WriteLine(ex.Message); Console.ReadLine(); }
}
and this is the exception on the output window:
Exception thrown: 'System.Runtime.InteropServices.COMException' in mscorlib.dll PasteSpecial method of Range class failed
Any help will be appreciated, I'm not just looking for a fix but also to undertand what is wrong with my code, thanks!