Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Aug 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copying text from Word to new Excel workbook using macro

    I used this forum and found some code that allowed me to scan a document for any sentances that were in bold and copy it to a new document. I added an "and" statement to find anything bold and italic. How would I change the code to copy the data into a new excel worksheet instead of a new word document.

    Also right now it creates a paragraph after each sentance, how would I adjust that its just one line below?

    Thanks!

    Dim docCur As Document
    Dim docNew As Document
    Dim snt As Range
    Dim rng As Range



    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Set docCur = ActiveDocument
    Set docNew = Documents.Add
    For Each snt In docCur.Sentences
    If snt.Bold = True And snt.Italic = True Then
    snt.Copy
    Set rng = docNew.Range
    rng.Collapse wdCollapseEnd
    rng.Paste
    rng.InsertParagraphAfter
    End If
    Next snt

    ExitHandler:
    Application.ScreenUpdating = True
    Set snt = Nothing
    Set docNew = Nothing
    Set docCur = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Copying content to an Excel workbook is a whole lot more involved than copying to another document. That's because, you already know Word is running when copying between documents - and you're doing so within the same application.

    To copy to Excel, however, you need to either start a new Excel session or hook into an existing one. Furthermore, you can't just copy the content to a new workbook the way you can to a new document, because Excel uses worksheets and each worksheet has a tabular cell structure. Consequently, you have to tell Excel which cells in which worksheet to use for the output. So, which cells in which worksheet do you want to use for the output? And what are the rules that determine which content from the document goes where?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Modifying your code to output your bold AND italic paragraphs to excel is simple enough. This example assumes that Excel is closed initially.
    Code:
    Sub TheBoldAndTheExcelful()
      Dim docCur As Document
      Dim snt As Range
      Dim i As Integer
      'Requires a reference to the 'Microsoft Excel XX.0 Object Library'
      Dim appXL As Excel.Application, xlWB As Excel.Workbook, xlWS As Excel.Worksheet
      
      'This assumes excel is currently closed
      Set appXL = CreateObject("Excel.Application")
      appXL.Visible = True
      Set xlWB = appXL.Workbooks.Add
      Set xlWS = xlWB.Worksheets(1)
      
      On Error GoTo ErrHandler
      Application.ScreenUpdating = False
      
      Set docCur = ActiveDocument
      
      For Each snt In docCur.Sentences
        If snt.Bold = True And snt.Italic = True Then
          i = i + 1
          xlWS.Cells(i, 1).Value = snt.Text
        End If
      Next snt
      
    ExitHandler:
      Application.ScreenUpdating = True
      Set snt = Nothing
      Exit Sub
      
    ErrHandler:
      MsgBox Err.Description, vbExclamation
      Resume ExitHandler
    End Sub
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #4
    New Lounger
    Join Date
    Aug 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Andrew Lockton View Post
    Modifying your code to output your bold AND italic paragraphs to excel is simple enough. This example assumes that Excel is closed initially.
    Code:
    Sub TheBoldAndTheExcelful()
      Dim docCur As Document
      Dim snt As Range
      Dim i As Integer
      'Requires a reference to the 'Microsoft Excel XX.0 Object Library'
      Dim appXL As Excel.Application, xlWB As Excel.Workbook, xlWS As Excel.Worksheet
      
      'This assumes excel is currently closed
      Set appXL = CreateObject("Excel.Application")
      appXL.Visible = True
      Set xlWB = appXL.Workbooks.Add
      Set xlWS = xlWB.Worksheets(1)
      
      On Error GoTo ErrHandler
      Application.ScreenUpdating = False
      
      Set docCur = ActiveDocument
      
      For Each snt In docCur.Sentences
        If snt.Bold = True And snt.Italic = True Then
          i = i + 1
          xlWS.Cells(i, 1).Value = snt.Text
        End If
      Next snt
      
    ExitHandler:
      Application.ScreenUpdating = True
      Set snt = Nothing
      Exit Sub
      
    ErrHandler:
      MsgBox Err.Description, vbExclamation
      Resume ExitHandler
    End Sub
    when i try to run this macro, i get a compile errror at this line of code: Dim appXL As Excel.Application


    My big issue with the current code that I have is the large spacing in between each copy and paste of bold/italic lines. I see that the "paragraph" line is causing this. How can I change that to be maybe one line between each action?

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Modia,

    To get rid of the compile error you need to set a reference to Excel:
    In the VBE window:
    Tools -> References
    ExcelLib.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    New Lounger
    Join Date
    Aug 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you both!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •