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!

Inward_SpiralAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.