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, May 08 '09 at 15:25

score 4 · Answer 1 · answered May 08 '09 at 15:47

4

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

answered May 08 '09 at 15:47

Lunatik

3,838
6
37
52

How do you access the newly created workbook with the new worksheet? – Chris Burgess May 08 '09 at 15:59
Nice answer Lunatik +1. Gern, you raise a good point. You'd have to access the workbook by name... which would be automatically generated. The Worksheet.SaveAs(string) approach has a similar problem in that you cannot get the workbook reference directly -- but in this case you at least do know the workbook name. So as long as you are willing to save the workbook (or want to) then the SaveAs approach is a little better. – Mike Rosenblum May 08 '09 at 19:21
1

Just a comment on the above, once you create the new workbook you can reference it as the `ActiveWorkbook` so you can then set the name, or prompt the user for it, perhaps in advance, by using `GetSaveAsFilename`. – Lunatik Mar 04 '11 at 13:58

score 1 · Accepted Answer · edited Jun 11 '12 at 05:19

1

Doh!

worksheet.SaveAs(Filename:=filePath)

edited Jun 11 '12 at 05:19

Siddharth Rout

147,039
17
206
250

answered May 08 '09 at 18:47

Chris Burgess

5,787
13
54
69

Yes, this works too if your goal is, ultimately, to save the workbook as well. Lunatik got it right though, you should give him the check mark for a correct answer. – Mike Rosenblum May 08 '09 at 19:13
Hmm.. ok, well there is at least one advantage to using .SaveAs(string) versus .Copy(): you will at least know the name of your workbook after the operation has completed. So this approach is actually better as long as one is willing (or needs) to save the workbook. – Mike Rosenblum May 08 '09 at 19:22

How to export Excel worksheets into new workbooks

2 Answers2

Linked