how copy one sheet in excel and then change some cells value?

• • Reply

  12 years ago

  by pkumar58

  Paramesh Kumar

  , United States

  Joined 13 years ago

  how copy one sheet in excel and then change some cells value? i wrote the below code and copy method work correctly but when i was change the value of some cells , my code change the last sheet but i want to select a new sheet (copy sheet) and change the value of some cells inside it. please help me

  
  

          Dim ObjExcel As New Microsoft.Office.Interop.Excel.Application()
          Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
          Dim WorkBooks As Microsoft.Office.Interop.Excel.Workbooks
          Dim WorkBook As Microsoft.Office.Interop.Excel.Workbook
          Dim WorkSheet As Microsoft.Office.Interop.Excel.Worksheet
  
          ObjExcel.Visible = True
          ObjExcel.UserControl = True
          System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
          ObjExcel.Workbooks.Open(Application.StartupPath + "\rptBAZ.xls")
  
  
  
  
  
          WorkBooks = ObjExcel.Workbooks
          WorkBook = WorkBooks.Item(1)
          WorkSheet = WorkBook.Worksheets(1)
          ObjExcel.Visible = True
  
  
          WorkSheet = WorkBook.Worksheets.Item(1)
          WorkSheet.Copy(WorkBook.Worksheets.Item(1))
  
          WorkSheet = WorkBook.Worksheets(2)
          WorkSheet.Range("E3", "F3").Value2 = 10
          WorkSheet.Range("G3").Value2 = 10
  
  
          System.Threading.Thread.CurrentThread.CurrentCulture = oldCI

  
  

  Report abuse
• • Reply

  12 years ago

  by Slicksim

  Simon Cooper

  Lancaster, United Kingdom

  Joined 15 years ago

  Hi,

  Been a while since I did some excel programming, however, i think what you need to do is to use the count of the worksheets, as the last item added will be the last item in the collection.

  so, you would use

  worksheet = workbook.worksheets.count

  Now, I think it will be a one based array, so the above should work.  If it is a zero based array, you will need to use

  worksheet = workbook.worksheets.count - 1

  HTH

  Simon C

  Report abuse