将Excel工作表复制到新工作簿中

本文介绍了将Excel工作表复制到新工作簿中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我有一张带有多张工作表的excel工作簿，并希望将每张工作表写在单独的工作簿中，为此我使用下面给出的代码，但是它写了一张空白页...... 请帮助一个人 谢谢 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 OneThanksDim MainWorkbook As Excel.WorkbookDim Filepath As StringPrivate 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 IfEnd SubPrivate 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"))NextSee 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工作表复制到新工作簿中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！