GreyFoxaBox
New Member
- Joined
- Jul 17, 2021
- Messages
- 1
- Office Version
- 2019
- Platform
- Windows
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
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