• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1206
  • Last Modified:

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

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!

0
Inward_Spiral
Asked:
Inward_Spiral
1 Solution
 
omegaomegaDeveloperCommented:
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)
        wshTest.Copy(Before:=wshTest)
        wbkTest.Close(SaveChanges:=True)
        xlaTest.Quit()

Open in new window

0
 
Inward_SpiralAuthor Commented:
That did it, thanks!
0
 
jvs7829Commented:
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
        'MsgBox(MyWBName)
        '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
 
        oBook.Close(SaveChanges:=True)
        oExcel.Application.Quit()
        oExcel = Nothing
        'oExcel.Save()
    End Sub

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now