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

  • 12 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


  • 12 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

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.” - Rich Cook