Copy excel sheet into new workbook

Question

0.00/5 (No votes)

See more:

I have a excel workbook with multiple sheets and want to write each sheet in separate work book, for this i am using code given below BUT its writing a Blank Sheet..

Please Help Some One
Thanks

VB

Dim MainWorkbook As Excel.Workbook
Dim Filepath As String
Private Sub btnbrows_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnbrows.Click
    Dim ExcelObj As Excel.Application
    ExcelObj = New Excel.Application
    OpenFileDialog1.Title = "Select Xls File To Import Employee"
    'OpenFileDialog1.Filter = "xls Files|*.xlsx"
    If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
        Filepath = OpenFileDialog1.FileName
        MainWorkbook = ExcelObj.Workbooks.Open(OpenFileDialog1.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
    End If
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
    Dim Objexcel As New Excel.Application
    Dim theWorkbook As Excel.Workbook
    Dim theWorksheet As Excel.Worksheet
    For Each excelsht As Excel.Worksheet In MainWorkbook.Sheets
        theWorkbook = Objexcel.Workbooks.Add()
        theWorksheet = theWorkbook.Sheets("SHEET1")
        theWorksheet = excelsht
        theWorkbook.SaveAs("D:\New Folder\" + excelsht.Name)
    Next
    System.Runtime.InteropServices.Marshal.ReleaseComObject(theWorkbook)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(MainWorkbook)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(theWorksheet)
    Objexcel.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(Objexcel)
    theWorksheet = Nothing
    theWorkbook = Nothing
    MainWorkbook = Nothing
    Objexcel = Nothing
    MessageBox.Show("DONE !!")
End Sub

Posted 1-Jan-15 22:58pm

Naim_86

Updated 1-Jan-15 23:20pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2015-01-01T23:38:00

Try this:

VB

For Each excelsht As Excel.Worksheet In MainWorkbook.Worksheets
    excelsht.Copy() 'copy sheet into new workbook
    theWorksheet = Objexcel.ActiveSheet
    theWorkbook = theWorksheet.Parent
    theWorkbook.SaveAs(String.Concat("D:\New Folder\", excelsht.Name, ".xlsx"))
Next

See Worksheet.Copy Method (Excel)[^]

[EDIT]
If Parent[^] won't work, try this:

VB

For Each excelsht As Excel.Worksheet In MainWorkbook.Worksheets
    theWorkbook = Objexcel.Workbooks.Add()
    theWorksheet = theWorkbook.Worksheets(1)
    excelsht.Copy(Before:=theWorkbook.Worksheets(theWorksheet)) 'copy sheet into new workbook
    theWorkbook.SaveAs(String.Concat("D:\New Folder\", excelsht.Name, ".xlsx"))
Next