|
#1
|
||||
|
||||
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] |
|
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 |