VBA Beginner - Copying Data from word file to a new excel document

#1
Hi,

I am an Excel VBA noob. Could you please help me correct this code? The Excel file that gets created is corrupted/blank.

Thanks,
GreyFox

Sub CopyWordtoExcel()

Dim FileName As String
Dim myExcel As Object
Dim myWb As Object
Dim MainData As String

FileName = ActiveDocument.Name

ActiveDocument.Tables(1).Select
Selection.Cut
Selection.TypeParagraph
Selection.InsertBreak Type:=wdPageBreak
Selection.PasteAndFormat (wdFormatOriginalFormatting)
Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Name:="1"
Selection.GoTo What:=wdGoToBookmark, Name:="\page"
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.GoTo What:=wdGoToBookmark, Name:="\page"
Selection.Delete Unit:=wdCharacter, Count:=1

MainData = ActiveDocument.Content

Set myExcel = CreateObject("Excel.Application")
Set myWb = myExcel.Workbooks.Add
Range("A1").Value = MainData

Application.DisplayAlerts = False
myWb.SaveAs FileName:="XX" & "\" & FileName & ".xls"

Application.DisplayAlerts = False

myWb.Close False
Set myWb = Nothing
Set myExcel = Nothing

End Sub