Click here to Skip to main content
14,829,981 members
Please Sign up or sign in to vote.
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
Updated 1-Jan-15 23:20pm
v2

1 solution

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
   
v2
Comments
Naim_86 2-Jan-15 6:37am
   
its throughing error on .parent
Maciej Los 2-Jan-15 6:39am
   
What's the message?
Naim_86 2-Jan-15 6:42am
   
Object reference not set
Naim_86 3-Jan-15 0:08am
   
A ERROR MESSAGE THROUGH

Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
On
excelsht.Copy(Before:=theWorkbook.Worksheets(theWorksheet))

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900