locked
Problem in copying one excel sheet to another excel sheet programetically

    Question

  • Hi,

    I am working on window appliction using vs 2008, .net framework 3.5. I have made a function to copy content of one excel sheet of office 2007 to another excelsheet.. Code for this is following..

    xlSheetCopy.UsedRange.Copy(Type.Missing.Value)
    xlSheetPaste.PasteSpecial(Type.Missing.Value, Type.Missing.Value)

    This works fine.. but the problem is there are some formulas in the excel sheet which is to be copied. Excel automatically adjusts the cell references in that formula to the new location..e.g.   ='D:\[excelCopy.xlsx]sheet3'!$F$6 ... I want that it should be copied as a text only like ='sheet3'!$F$6 ...

    Thanks


    MMC
    Tuesday, July 21, 2009 8:58 AM

Answers

  • Hi MMC123,

    This mechanism will ensure that the formula can be used correctly.
    There are one workaroud for your problem:
    You can modify the formula after copy. Here is an example:

    Imports Microsoft.Office.Interop.Excel
    Imports System.Text
    
    Public Class Form1
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim filePath As String = "D:\Book2.xls"
            Dim oExcel As Object = CreateObject("Excel.Application")
            Dim oBook As Object = oExcel.Workbooks.Open(filePath)
    
            Dim xlSheet As Object = oBook.Worksheets(2)
    
            Dim temp() As String = filePath.Split(".")(0).Split("\")
            Dim sb As New StringBuilder
            Dim i As Integer = 0
    
            For i = 0 To temp.Count - 2
                sb.Append(temp(i) & "\")
            Next
    
            sb.Append("[" & temp(i) & "]")
    
            'sb.ToString = "D:\[Book1.xls]"
            xlSheet.Range("B14").Formula = xlSheet.Range("B14").Formula.ToString.Replace(sb.ToString, "")
    
            xlSheet.SaveAs("D:\Book2.xls", True)
            oExcel.Quit()
        End Sub
    End Class

    Does this works for you? If you have any questions or concerns, please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng

    • Marked as answer by Yichun_Feng Tuesday, July 28, 2009 1:33 AM
    Friday, July 24, 2009 6:41 AM
  • 'Here is a code for that in vb.net

     Dim filePath As String = "D:\Book3.xls"
            Dim filePath1 As String = "D:\Book5.xls"
            Dim xlobj As New Microsoft.Office.Interop.Excel.Application()
            Dim w As Workbook
            Dim w1 As Workbook
            Dim s As Worksheet
            Dim s1 As Worksheet

            Try
                w = xlobj.Workbooks.Open(filePath)
                w1 = xlobj.Workbooks.Open(filePath1)
                s = w.Worksheets.Item(1)
                s1 = w1.Worksheets.Item(1)

    ' it will copy and paste sheet from one to another with formula
                s.UsedRange.Copy()
                s1.PasteSpecial()
                s1.UsedRange.Formula = s.UsedRange.Formula


                w.Save()
                w1.Save()
                w.Close()
                w1.Close()
            Catch ex As Exception
                w.Save()
                w1.Save()
                w.Close()
                w1.Close()
            End Try
    • Marked as answer by MMC123 Monday, August 10, 2009 8:35 AM
    Tuesday, August 04, 2009 9:06 AM

All replies

  • Hi MMC123,

    This mechanism will ensure that the formula can be used correctly.
    There are one workaroud for your problem:
    You can modify the formula after copy. Here is an example:

    Imports Microsoft.Office.Interop.Excel
    Imports System.Text
    
    Public Class Form1
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim filePath As String = "D:\Book2.xls"
            Dim oExcel As Object = CreateObject("Excel.Application")
            Dim oBook As Object = oExcel.Workbooks.Open(filePath)
    
            Dim xlSheet As Object = oBook.Worksheets(2)
    
            Dim temp() As String = filePath.Split(".")(0).Split("\")
            Dim sb As New StringBuilder
            Dim i As Integer = 0
    
            For i = 0 To temp.Count - 2
                sb.Append(temp(i) & "\")
            Next
    
            sb.Append("[" & temp(i) & "]")
    
            'sb.ToString = "D:\[Book1.xls]"
            xlSheet.Range("B14").Formula = xlSheet.Range("B14").Formula.ToString.Replace(sb.ToString, "")
    
            xlSheet.SaveAs("D:\Book2.xls", True)
            oExcel.Quit()
        End Sub
    End Class

    Does this works for you? If you have any questions or concerns, please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng

    • Marked as answer by Yichun_Feng Tuesday, July 28, 2009 1:33 AM
    Friday, July 24, 2009 6:41 AM

  • Hi,Yichun Feng


    Thanks for Reply.. I tried above code and there is no error in it.. but nothing happened with the formula. Still its like 'D:\[excelCopy.xlsx]sheet3'!$F$6'..Suggest me if there is any other way..

    Thanks


    MMC
    Saturday, July 25, 2009 8:53 AM
  • Hi MMC123,

    My code would change the formula in certain cell. In the example it is B14. So if you want to apply it, you may put the cell number which has the formula into a list or array. Then traverse the arry to change the formula. And you can use Msgbox to show the xlSheet.Range("B14").Formula.ToString before you set it.

     If you have any questions or concerns, please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng


    Monday, July 27, 2009 2:11 AM
  • Hi Yichun Feng,

    I tried again and it works fine.. but there is one problem.. I don't know which cells are having formula .. there are so many cells which are having formula.. so have to parse throgh all cell and then if a cell contains formula have to replace it.. which is very time consuming at run time...

    thanks

    MMC
    Saturday, August 01, 2009 9:35 AM
  • Hi MMC123,

    I find another similar thread for your problem.
    You may refer to it: http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/0db941ba-7c57-471b-b64c-90aab6d56aa2

    Best Regards

    Yichun Feng


    Sunday, August 02, 2009 7:44 AM
  • 'Here is a code for that in vb.net

     Dim filePath As String = "D:\Book3.xls"
            Dim filePath1 As String = "D:\Book5.xls"
            Dim xlobj As New Microsoft.Office.Interop.Excel.Application()
            Dim w As Workbook
            Dim w1 As Workbook
            Dim s As Worksheet
            Dim s1 As Worksheet

            Try
         &
    Tuesday, August 04, 2009 9:06 AM
  • 'Here is a code for that in vb.net

     Dim filePath As String = "D:\Book3.xls"
            Dim filePath1 As String = "D:\Book5.xls"
            Dim xlobj As New Microsoft.Office.Interop.Excel.Application()
            Dim w As Workbook
            Dim w1 As Workbook
            Dim s As Worksheet
            Dim s1 As Worksheet

            Try
                w = xlobj.Workbooks.Open(filePath)
                w1 = xlobj.Workbooks.Open(filePath1)
                s = w.Worksheets.Item(1)
                s1 = w1.Worksheets.Item(1)

    ' it will copy and paste sheet from one to another with formula
                s.UsedRange.Copy()
                s1.PasteSpecial()
                s1.UsedRange.Formula = s.UsedRange.Formula


                w.Save()
                w1.Save()
                w.Close()
                w1.Close()
            Catch ex As Exception
                w.Save()
                w1.Save()
                w.Close()
                w1.Close()
            End Try
    • Marked as answer by MMC123 Monday, August 10, 2009 8:35 AM
    Tuesday, August 04, 2009 9:06 AM