#1
|
|||
|
|||
Extracting Data from Word Documents
Hi I have a folder with a 100 word documents, is there a way I can extra certain information from the files and deposit it in excel. I want to take the name, address and amount I have quoted from each file then add this to a new row in an excel file. Is this asking too much of the software ? David
|
#2
|
|||
|
|||
Unless there is some way for the software to identify these bits of data, it is asking too much.
|
#3
|
||||
|
||||
It is possible to do but you would need to write a macro that opens each file, finds the relevant information and copies it to your excel sheet.
This is not a trivial exercise but if your 100 documents are very consistent it is certainly possible.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#4
|
||||
|
||||
For some macros that show how you might go about this for a single document, see:
https://www.msofficeforums.com/word-...acters-up.html https://www.msofficeforums.com/word/...doc-excel.html For code demonstrating how to process an entire folder and export Word data to Excel, see: https://www.msofficeforums.com/word-...html#post64259 If your data are held in formfields or content controls, the following Excel macro might be more useful. It extracts data from all formfields & content controls in all Word documents in the selected folder and populates the first available row in the active Excel worksheet with the data for each document. 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 FmFld As Word.FormField, CCtrl As Word.ContentControl Dim strFolder As String, strFile As String Dim WkSht As Worksheet, c As Long, r 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 = 0 For Each FmFld In .FormFields c = c + 1 With FmFld Select Case .Type Case Is = wdFieldFormCheckBox WkSht.Cells(r, c) = .CheckBox.Value Case Else WkSht.Cells(r, c) = .Result End Select End With Next For Each CCtrl In .ContentControls c = c + 1 With CCtrl Select Case .Type Case Is = wdContentControlCheckBox WkSht.Cells(r, c) = .Checked Case wdContentControlDate, wdContentControlDropdownList, wdContentControlRichText, wdContentControlText WkSht.Cells(r, c) = .Range.Text Case Else End Select End With Next .Close SaveChanges:=False End With strFile = Dir() Wend wdApp.Quit 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 c = 0 to: c = 1: WkSht.Cells(r, c) = strFile
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Extracting data, pivot tables, and maybe VBA help? | rbexcelhelp | Excel Programming | 3 | 05-09-2015 12:13 AM |
extracting data from word docs | stubevh | Word | 2 | 03-04-2015 06:27 PM |
formula writing for extracting data | jennamae | Excel | 1 | 11-15-2013 08:40 PM |
Extracting data from excel | Eric855 | Word | 6 | 07-25-2013 08:02 AM |
Extracting Contacts Data from Excel | Caesar | Outlook | 1 | 05-08-2011 05:54 AM |