0

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!

dreamheart
  • 63
  • 6
  • There are a lot of existing questions about this error, you find them by googling the error message. I suspect that [aucuparia's answer](https://stackoverflow.com/questions/45195868/paste-special-error-1004-pastespecial-method-of-range-class-failed) explains the most. – Hans Passant Oct 18 '18 at 15:15
  • I did my reserach before posting the question but none of the solutions i found worked for me. I tried also the ones on your link and unfortunatelly I keep getting the same error. – dreamheart Oct 18 '18 at 17:37

0 Answers0