none
Need macro to copy text from word to excel - help

    Question

  • Hi there,

    I need a little help, I a word document with near enough the same text as a excel document, so i was wondering if there is anyway to copy from word to excel maybe by a macro?

    Thanks!

    K

    Tuesday, October 16, 2012 3:52 PM

Answers

  • Hi,

    How about try the following:

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

    Sub Exportwordtoexcel()
        Dim wordDoc As Object
        Dim oXL As Excel.Application
        Dim DocTarget As Word.Document
        Dim Target As Excel.Workbook
        Dim tSheet As Excel.Worksheet
        
    Set wordDoc = GetObject(, "word.application")
    wordDoc.Selection.WholeStory
    Selection.Copy
    
    '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
    Set tSheet = Target.Sheets(1)
    tSheet.Paste
    
    End Sub

    Best regards,

    Rex Zhang

    TechNet Community Support

    Thursday, October 18, 2012 7:55 AM
  • For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

    Cheers
    Paul Edstein
    [MS MVP - Word]


    Thursday, October 18, 2012 8:40 PM

All replies

  • Hi,

    How about try the following:

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

    Sub Exportwordtoexcel()
        Dim wordDoc As Object
        Dim oXL As Excel.Application
        Dim DocTarget As Word.Document
        Dim Target As Excel.Workbook
        Dim tSheet As Excel.Worksheet
        
    Set wordDoc = GetObject(, "word.application")
    wordDoc.Selection.WholeStory
    Selection.Copy
    
    '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
    Set tSheet = Target.Sheets(1)
    tSheet.Paste
    
    End Sub

    Best regards,

    Rex Zhang

    TechNet Community Support

    Thursday, October 18, 2012 7:55 AM
  • For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

    Cheers
    Paul Edstein
    [MS MVP - Word]


    Thursday, October 18, 2012 8:40 PM