Creating an Excel document from MS Word

I have just come across the need to create an Excel workbook from a MS Word macro. I have simplified this code for the blog to simply move all text from Word into a new Excel workbook. This will provide a solid starting point for future use.

Option Explicit

Public Sub FormatInExcel()

On Error GoTo Err_Handler

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim oDoc As Object

Set oDoc = ThisDocument

‘ Start Excel and get Application object.
Set oXL = CreateObject(“Excel.Application”)
oXL.Visible = True

‘ Get a new workbook.
Set oWB = oXL.Workbooks.Add
Set oSheet = oWB.ActiveSheet

‘ Do work
oDoc.ActiveWindow.Selection.WholeStory
oDoc.ActiveWindow.Selection.Copy

oSheet.Range(“A1″).PasteSpecial xlPasteValues

‘ Release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing
Set oDoc = Nothing

Exit Sub

Err_Handler:
MsgBox Err.Description, vbCritical, “Error: ” & Err.Number

End Sub

Happy coding!