Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-27-2017, 08:28 AM
bartvdg bartvdg is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Sep 2017
Posts: 2
bartvdg is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 09-27-2017, 02:09 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,648
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
  #3  
Old 09-29-2017, 06:05 AM
bartvdg bartvdg is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Sep 2017
Posts: 2
bartvdg is on a distinguished road
Default

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
Attached Files
File Type: docx example.docx (112.0 KB, 5 views)
Reply With Quote
  #4  
Old 09-29-2017, 08:34 AM
Charles Kenyon Charles Kenyon is offline Windows 10 Office 2013
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 4,530
Charles Kenyon is just really niceCharles Kenyon is just really niceCharles Kenyon is just really niceCharles Kenyon is just really nice
Default

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.
Reply With Quote
  #5  
Old 09-29-2017, 03:13 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,648
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
It includes a folder browser, so all you need do is select the folder with all the forms that are to be processed.

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
[MS MVP - Word]
Reply With Quote
Reply

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


All times are GMT -7. The time now is 03:33 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft