#1
|
|||
|
|||
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. |
#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
|
|||
|
|||
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? |
#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
|
|||
|
|||
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? |
#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 |