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

[GreyFoxaBox]

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

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA Beginner - Copying Data from word file to a new excel document
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.