![]() |
|
|
|
#1
|
|||
|
|||
|
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.
|
|
#2
|
||||
|
||||
|
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 |
|
#3
|
|||
|
|||
|
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
|
|
#4
|
|||
|
|||
|
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? |
|
#5
|
||||
|
||||
|
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 |
|
#6
|
|||
|
|||
|
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? |
|
#7
|
||||
|
||||
|
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 |
|
#8
|
|||
|
|||
|
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
|
|
| 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 |
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 |