Wednesday, May 27, 2015

Copy multiple pages word to multiple sheets excel vba


Sub Copywordtoexcel()
    Dim wordDoc As Object
    Dim oXL As Excel.Application
    Dim DocTarget As Word.Document
    Dim Target As Excel.Workbook
    Dim tSheet As Excel.Worksheet
    Dim i As Integer
   
    'If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
   ExcelWasNotRunning = True
   Set oXL = New Excel.Application
End If

oXL.Visible = True
Set Target = oXL.Workbooks.Add
    ' Run loop
For i = 1 To 42
    Selection.GoTo What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=i
    ' selects content of page i
    Selection.Extend
    Application.Browser.Next
    ' copies selected page
    Selection.Copy
    ' Copy to excel
 Set tSheet = Target.Sheets.Add
    tSheet.Paste
    tSheet.Name = "page" & i 
Next i

End Sub
Note: Before runing the code in Word, please enable the Reference "Microsoft Excel xx.0 Object Library" in VBE.

Reference:
http://vbcity.com/forums/t/163889.aspx
https://social.technet.microsoft.com/Forums/office/en-US/0a73df0b-096b-40eb-a65a-9b16bc6a57ed/need-macro-to-copy-text-from-word-to-excel-help?forum=word

No comments:

Post a Comment