#1
|
|||
|
|||
Consolidating data from Word into one Excel
I would like to consolidate several word documents into one Excel to get a central view. The layout of the Word doc is the same for all Word docs.
Thanks Bart |
#2
|
||||
|
||||
You need to explain more about what you're trying to achieve; what you mean by 'to get a central view' is far from clear.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
I want to have a central (consolidated) view of the Word documents data in one Excel sheet. The word documents are located in one folder and have the same layout.
Example of one Word document attached, there are a few hundred of those where I want to have the yellow marked data into one excel sheet. Regards Bart |
#4
|
|||
|
|||
Word needs a way to know what it is you want consolidated. Bookmarks are one way. A special style is another. Some of your data has bookmarks, but not all. Some has labels, but not all.
Off hand, for me, it would probably be simplest to copy and paste from the Word documents one at a time. If each of your documents has identical layout, with your data in exactly the same place, it should be possible to automate, but, for me, it would be a challenge. Once I was done with the manual conversion, I would probably design a data entry form of some sort in Word (or Access) and use that in the future. |
#5
|
||||
|
||||
Try the following Excel macro, which you add to the workbook the data are to be imported into.
Code:
Sub GetFormData() 'Note: this code requires a reference to the Word object model. 'See under the VBE's Tools|References. Application.ScreenUpdating = False Dim wdApp As New Word.Application, wdDoc As Word.Document Dim wdRng As Word.Range, wdFmFld As Word.FormField Dim strFolder As String, strFile As String, strTxt As String Dim WkSht As Worksheet, r As Long, c As Long strFolder = GetFolder If strFolder = "" Then Exit Sub Set WkSht = ActiveSheet r = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row strFile = Dir(strFolder & "\*.doc", vbNormal) While strFile <> "" r = r + 1 Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False) With wdDoc c = 1 With .Tables(1) Set wdRng = .Cell(1, 3).Range With wdRng .End = .End - 1 strTxt = Replace(.Text, vbCr, "¶") WkSht.Cells(r, c) = strTxt End With For Each wdFmFld In .Range.FormFields With wdFmFld Select Case .Type Case Is = wdFieldFormCheckBox Case Else: c = c + 1: WkSht.Cells(r, c) = Replace(wdFmFld.Result, vbCr, "¶") End Select End With Next End With c = c + 1: WkSht.Cells(r, c) = Replace(.FormFields("Text15").Result, vbCr, "¶") With .Tables(2) For Each wdFmFld In .Range.FormFields c = c + 1 WkSht.Cells(r, c) = Replace(wdFmFld.Result, vbCr, "¶") Next End With With .Tables(5) For Each wdFmFld In .Range.FormFields c = c + 1 WkSht.Cells(r, c) = Replace(wdFmFld.Result, vbCr, "¶") Next End With .Close SaveChanges:=False End With strFile = Dir() Wend wdApp.Quit WkSht.UsedRange.Replace "¶", Chr(10) Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing Application.ScreenUpdating = True End Sub Function GetFolder() As String Dim oFolder As Object GetFolder = "" Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0) If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path Set oFolder = Nothing End Function For PC macro installation & usage instructions, see: http://www.gmayor.com/installing_macro.htm. Although these instructions are for Word, the principles are the same for Excel.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Word & Excel 2010 - Best Options To Auto Insert MySQL & Excel Data? | Hoser | Word | 1 | 03-17-2017 03:47 PM |
Consolidating various word docs in one | Max Downham | Word | 6 | 11-23-2015 05:07 PM |
Populate Word Drop-down list with Excel column then auto fill form fields with Excel data | Faldinio | Word VBA | 7 | 10-19-2014 06:03 AM |
Need help consolidating data from separate sheets | tiwas | Excel | 1 | 10-07-2014 04:57 AM |
Consolidating data using Macro | mrjamez | Excel Programming | 2 | 05-22-2012 06:50 AM |