How to export Excel worksheets into new workbooks

Question

I have a bunch of Excel workbooks that contain multiple worksheets. I want to loop through each workbook and export each worksheet into it's own new workbook. I want one worksheet in each new workbook.

Here's what I've got so far:

   Sub ExportWorksheet(ByVal worksheet As Excel.Worksheet, ByVal filePath As String)
      Dim xlApp As Excel.Application = New Excel.ApplicationClass
      Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Add
      worksheet.Select()
      worksheet.Copy()
      xlWorkBook.Worksheets.Add()
      worksheet.Paste(Destination:=xlWorkBook)

      xlWorkBook.SaveAs(Filename:=filePath)

      xlWorkBook.Close(False)
      xlApp.Quit()
   End Sub

No. I get an error on the paste bits: System.Runtime.InteropServices.COMException was unhandled ErrorCode=-2146827284 Message="Exception from HRESULT: 0x800A03EC" Source="Microsoft.Office.Interop.Excel" — Chris Burgess

Lunatik Lunatik · Accepted Answer · 2009-05-08T15:47:24

Within Excel this would be accomplished by copying the worksheet to a new workbook, not by creating a new workbook then adding the worksheet to it. This is achieved by using Worksheet.Copy without specifying where in the workbook you want to place the copied worksheet.

More reading: http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.copy(VS.80).aspx

How to export Excel worksheets into new workbooks

2 Answers