VB.NET: How can I copy an Excel worksheet?

Inward_Spiral used Ask the Experts™
Hello all,

I'm trying to use VB.NET to automate some tedious manual Excel data entry we have going on. We have an Excel file where we keep track of some tests we're running. For every test, we add a new tab/worksheet to the Excel file.

Ideally, I'd like to use VB.NET to open the excel file, automatically make a copy of the "current" tab/worksheet, and replace the data with my new test results, so all the formatting and everything stays the same.

I know how to use VB.NET to edit cells in an excel worksheet, but am having trouble copying a specific sheet.

Any comments/suggestions would definitely be welcome, thanks!

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hello, Inward Spiral,

The code in the attached snippet works for me (i.e. copies the WorkSheet named "Sample" in the designated Workbook).

Perhaps if you show the code you are trying to use, someone can pinpoint the problem.
        Dim xlaTest As Excel.Application = CType(CreateObject("Excel.Application"), Excel.Application)
        xlaTest.Visible = True
        Dim wbkTest As Excel.Workbook = xlaTest.Workbooks.Open("D:\Tests\VB Trials\ExcelTests\TestWorkbook.xls")
        Dim wshTest As Excel.Worksheet = DirectCast(wbkTest.Worksheets("Sample"), Excel.Worksheet)

Open in new window


That did it, thanks!

What if I want to copy an Excel sheet and then only modify select cells and rename it based upon input to a textbox in a form?  I'm getting errors when debugging on renaming the worksheet.  When I turn that off, then I get errors on the "oSheet2.Cells.Range("D123") = TextBox1.Text".... saying that "Public member 'Cells' on type 'Boolean' not found."  I also get the message if I have "oSheet2.range("D123") = TextBox1.Text".  Any suggestions????  I'd appreciate some help on this.  Some of the text boxes hold file paths, others hold numeric values, and some are mixed text and numbers.  I'm not sure if there needs to be differences in how each is set up or not.

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim MyWBName As Object
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object
        Dim oSheet2 As Object
        oExcel = CreateObject("Excel.Application")
        oExcel.Visible = True
        MyWBName = TextBox26.Text
        'MyWBName = oExcel.GetOpenFilename()
        oBook = oExcel.Workbooks.Open(MyWBName)
        ' MsgBox("The current workbook is " & MyWBName)
        oSheet = oExcel.ActiveSheet
        'oSheet.Copy(After:=oSheet)  'copies worksheet, creating new worksheet with Scenario Name
        'oSheet2 = oSheet.Copy(After:=oBook.Sheets(3))
        ' MsgBox("The current sheet is " & oSheet)
        oSheet = oSheet.Copy(after:=oSheet)
        oSheet2 = oSheet
        'oSheet2.Name = ScenarioName   'Renames the new worksheet oSheet2
        'File Locations
        oSheet2.Cells.Range("D248").Value = TextBox9.Text                    'Snapshot File
        oSheet2.Cells.Range("D155").Value = TextBox10.Text                  'Currents File
        oSheet2.Cells.Range("D119").Value = TextBox17.Text                  'Met Data File
        oSheet2.Cells.Range("D557").Value = TextBox20.Text                  'Shoreline File
        oExcel = Nothing
    End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial