Copying worksheets between workbooks - Excel interop

I copy worksheets from one workbook and paste them into a second workbook. I use this code:

book = appExcel.Workbooks.Open(@"e:\tr\pliki\filename.xlsm",
                       Missing.Value, Missing.Value, Missing.Value,
                       Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                       Missing.Val开发者_StackOverflow社区ue, Missing.Value, Missing.Value, Missing.Value,
                       Missing.Value, Missing.Value, Missing.Value);

            Microsoft.Office.Interop.Excel.Global global = new Microsoft.Office.Interop.Excel.GlobalClass();
            sheet.Copy(Missing.Value, global.Sheets[5]);
            book.Save();

All works fine, but the problem is with the formulas. After worksheet has been copied formulas in it refer to values in the first workbook. The formulas paths contains paths to first workbook from which its was copied:

='E:\tr\pliki\[filename.xlsm]worksheetA'!A1:E2

and should be like this:

='worksheet'!A1:E2

Does anyone help me ?

I'm not aware of a clean way to do what your trying to do, anyway this is a workaround that could solve your problem. It's simply a matter of changing formulas to text before copying the worksheet and restore everything after that. I.e. you can replace all the = in your cells with some string which you know is not present in your data (e.g. something weird like X_X_X_X_X_X) and then revert to the initial situation.

The code could be something like this:

        Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
        Microsoft.Office.Interop.Excel.Workbook srcBook = appExcel.Workbooks.Open(@"c:\tmp\test.xls",
                   Missing.Value, Missing.Value, Missing.Value,
                   Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                   Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                   Missing.Value, Missing.Value, Missing.Value);
        Microsoft.Office.Interop.Excel.Workbook destBook = appExcel.Workbooks.Add(Missing.Value);
        Microsoft.Office.Interop.Excel.Worksheet srcSheet = (Microsoft.Office.Interop.Excel.Worksheet)srcBook.Worksheets[1];
        Microsoft.Office.Interop.Excel.Range usedRange = srcSheet.UsedRange;
        usedRange.Replace("=", "X_X_X_X_X_X", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        srcSheet.Copy(destBook.Worksheets[1], Missing.Value);
        Microsoft.Office.Interop.Excel.Worksheet destSheet = (Microsoft.Office.Interop.Excel.Worksheet)destBook.Worksheets[1];
        usedRange.Replace("X_X_X_X_X_X", "=", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        usedRange = destSheet.UsedRange;
        usedRange.Replace("X_X_X_X_X_X", "=", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);