将Excel工作表复制到新工作簿中 [英] Copy excel sheet into new workbook

问题描述

我有一张带有多张工作表的excel工作簿，并希望将每张工作表写在单独的工作簿中，为此我使用下面给出的代码，但是它写了一张空白页......



请帮助一个人

谢谢

 < span class =code-keyword> Dim  MainWorkbook  As  Excel.Workbook 
  Dim  Filepath  As   String  
  Private   Sub  btnbrows_Click（ ByVal  sender  As 系统。对象， ByVal  e 作为系统.EventArgs）句柄 btnbrows.Click 
  Dim  ExcelObj 作为 Exc el.Application 
 ExcelObj = 新 Excel.Application 
 OpenFileDialog1.Title =  选择要导入员工的Xls文件 
 '  OpenFileDialog1.Filter =xls Files | * .xlsx 
 如果 OpenFileDialog1.ShowDialog（）= Windows.Forms.DialogResult.OK 然后 
 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）
 结束 如果 
 结束  Sub  
 私有  Sub  Button1_Click（ ByVal  sender 作为系统。对象， ByVal  e  As  System.EventArgs）句柄 btnSave.Click 
  Dim  Objexcel 作为 新 Excel.Application 
  Dim  theWorkbook  As  Excel.Workbook 
  Dim  theWorksheet 作为 Excel.Worksheet 
 对于 每个 excelsht 作为 Excel.Worksheet 在 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 !!）
 结束  Sub  

解决方案

试试这个：

 对于 每个 excelsht 作为 Excel.Worksheet 在 MainWorkbook.Worksheets 
 excelsht.Copy（）'  copy工作表 
 theWorksheet = Objexcel.ActiveSheet 
 theWorkbook = theWorksheet.Parent 
 theWorkbook.SaveAs（ String 。 Concat（  D：\New Folder \，excelsht.Name， 。xlsx））
 下一页 

参见 Worksheet.Copy Method（Excel） [ ^ ]





如果父级 [ ^ ]不起作用，试试这个：

 对于 每个 excelsht 作为 Excel.Worksheet 在 MainWorkbook.Worksheets 
 theWorkbook = Objexcel.Workbooks.Add（）
 theWorksheet = theWorkbook.Worksheets（ 1 ） 
 excelsht.Copy（之前：= theWorkbook.Worksheets（theWorksheet））' 将工作表复制到新工作簿中 
 theWorkbook.SaveAs（ String  .Concat（  D：\New Folder \，excelsht.Name， 。xlsx））
 下一页 

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

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

解决方案

Try this:

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:

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

这篇关于将Excel工作表复制到新工作簿中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！