Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-12-2018, 01:25 PM
smbrush smbrush is offline Select and Copy from Word to Excel Windows 7 64bit Select and Copy from Word to Excel Office 2010 64bit
Novice
Select and Copy from Word to Excel
 
Join Date: Jun 2018
Posts: 3
smbrush is on a distinguished road
Default Select and Copy from Word to Excel


I have a Word 2010 document consisting of a numbered list of items. My goal is to automate the selection and copy of all the text for number 5 into an Excel spreadsheet. I'd appreciate some coding suggestions to make this possible. Thanks.
Reply With Quote
  #2  
Old 06-12-2018, 11:05 PM
gmayor's Avatar
gmayor gmayor is offline Select and Copy from Word to Excel Windows 10 Select and Copy from Word to Excel Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

It is impossible to answer your question without more information about the document and how it is laid out - what the 'text' in question is and where in which worksheet the data is to go.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 06-13-2018, 07:33 AM
d4okeefe d4okeefe is offline Select and Copy from Word to Excel Windows 10 Select and Copy from Word to Excel Office 2016
Advanced Beginner
 
Join Date: Apr 2013
Posts: 77
d4okeefe is on a distinguished road
Default

You can create an Excel workbook within Word. First, add a Reference to Excel in Word VBA (Tools -> References -> Microsoft Excel 16.0 Object Library). Then you can run the following.
Code:
Sub createExcelWkbk()
    Dim obj As Excel.Application
    Set obj = New Excel.Application
    obj.Visible = True
    
    Dim wkbk As Excel.Workbook
    Set wkbk = obj.Workbooks.Add
End Sub
From here, you can use a range object in Word to add data to the Workbook.
Reply With Quote
  #4  
Old 06-15-2018, 05:26 AM
smbrush smbrush is offline Select and Copy from Word to Excel Windows 7 64bit Select and Copy from Word to Excel Office 2010 64bit
Novice
Select and Copy from Word to Excel
 
Join Date: Jun 2018
Posts: 3
smbrush is on a distinguished road
Default Response for more information

I have Word 2010 document. The entire document consists of a numbered list starting from 1. I'd like to create a VBA procedure that will select all the text in number 5 and all bulleted subsections within. Then, I want to copy the selection to an Excel spreadsheet that already exists.

What more information do you need?
Reply With Quote
  #5  
Old 06-15-2018, 06:57 AM
gmayor's Avatar
gmayor gmayor is offline Select and Copy from Word to Excel Windows 10 Select and Copy from Word to Excel Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

A example of the document that shows the type of thing to be processed, and exactly where in the worksheet you want the various parts - especially if you want to repeat the exercise with different listed items.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #6  
Old 06-15-2018, 07:02 AM
smbrush smbrush is offline Select and Copy from Word to Excel Windows 7 64bit Select and Copy from Word to Excel Office 2010 64bit
Novice
Select and Copy from Word to Excel
 
Join Date: Jun 2018
Posts: 3
smbrush is on a distinguished road
Default Further Response

The documents I'm dealing with are proprietary to the company I work for so I can't attach them. Basically, I just have a document with a numbered list set up by the numbering button on the paragraph tab of the home ribbon.

Is it possible to select the text of a particular number in this kind of numbered list with VBA code?
Reply With Quote
  #7  
Old 06-15-2018, 07:39 AM
Guessed's Avatar
Guessed Guessed is online now Select and Copy from Word to Excel Windows 10 Select and Copy from Word to Excel Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,969
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

It is if you are using the Heading styles for this. But if you don't provide a sample document formatted as per your documents then we are only guessing and likely to be wrong.

For instance, if this magic paragraph was the first heading in the document you could go to it with
Set rng = ActiveDocument.Range.GoTo(wdGoToHeading, wdGoToFirst)

You can examine the options for the GoTo command here https://msdn.microsoft.com/en-us/VBA...to-method-word to see if you can find the right combination to always hit your section.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #8  
Old 06-15-2018, 08:43 AM
d4okeefe d4okeefe is offline Select and Copy from Word to Excel Windows 10 Select and Copy from Word to Excel Office 2016
Advanced Beginner
 
Join Date: Apr 2013
Posts: 77
d4okeefe is on a distinguished road
Default

I drew this up just now. Hope it helps.

As mention in the code comments, you need to add a reference to the Excel Object library. I use Office 2016, so my object library is named <Microsoft Excel 16.0 Object Library>. Yours may be slightly different -- something like <Microsoft Excel 11.0 Object Library>.

Code:
Sub find_no_5_and_paste_in_worksheet()
    ' Get text from list item 5 and subitems
    Dim d As Document: Set d = ActiveDocument
    Dim p As Paragraph
    Dim arr(1 To 100) As String
    Dim x As Integer: x = 1
    Dim five_found As Boolean
    
    For Each p In d.Paragraphs
        Dim lst_str As String
        lst_str = p.Range.ListFormat.ListString
        'p.Range.Select
        
        ' Adjust if necessary:
        ' In your list, "5." may look like "(5)" or "5)"
        If lst_str = "6." Then Exit For
        If lst_str = "5." Or five_found Then
            five_found = True
            arr(x) = p.Range.Text
            x = x + 1
        End If
    Next p
    
    ' open Excel and paste to worksheet for this to work, 
    ' you need a reference to <Microsoft Excel 16.0 Object Library>
    ' look in Tools -> References
    Dim obj As Excel.Application
    Set obj = New Excel.Application
    obj.Visible = True
    
    Dim wkbk As Excel.Workbook
    Set wkbk = obj.Workbooks.Add
    
    Dim wkst As Excel.Worksheet
    Set wkst = wkbk.ActiveSheet
    
    ' adjust the wkst.Cells(y, 1) assignment if you
    ' want this pasted somewhere else on the sheet
    ' y is the row, and 1 is the column
    Dim y As Integer
    For y = 1 To UBound(arr)
        If arr(y) <> "" Then
            'Debug.Print arr(y)
            wkst.Cells(y, 1) = arr(y)
        End If
    Next
End Sub
Reply With Quote
Reply

Tags
copy, select



Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel VBA - Select All in Word Doc & Update Fields RMerckling Excel Programming 1 04-27-2018 01:47 PM
Word 2007-cannot select or copy image calvin-c Word 2 01-05-2017 02:26 PM
Select and Copy from Word to Excel How to select and copy to clipboard an entire document except for a paragraph and keep formatting TD_123 Word VBA 7 06-16-2015 03:30 PM
Select Every Email in Word or Excel?!? arielrivera67 Excel 2 07-13-2014 09:54 PM
how to copy all ms word tables into excel rehan129 Word 0 01-28-2012 10:17 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:20 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft