G
Greg Maxey
Folks I don't dabble much in Excel VBA/programming so please be gentle.
I have managed to cobble together some code (bits and pieces found on the
internet) that extracts data from a collection of Word2007 documents and
puts that data into an Excel workbook. The source of the data is
ContentControls in the individual documents. I have a folder for
unprocessed documents and one for documents that have been processed. Here
is the code:
Option Explicit
Public Const pFolder As String = "F:\My Documents\Word\Data Extractor\"
Sub ExtractData()
Dim oCC As ContentControl
Dim fso As Scripting.FileSystemObject
Dim fsDir As Scripting.Folder
Dim fsFile As Scripting.File
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim vColumn As Integer
Dim vLastRow As Integer
Dim x As Integer
Dim vFileName As String
vLastRow = ActiveSheet.UsedRange.Rows.Count + 1
vColumn = 1
Set fso = New Scripting.FileSystemObject
Set fsDir = fso.GetFolder(pFolder & "To Process")
Set wdApp = New Word.Application
wdApp.Visible = True
For Each fsFile In fsDir.Files
wdApp.Documents.Open (fsFile)
Set myDoc = wdApp.ActiveDocument
For Each oCC In wdApp.Documents(myDoc).ContentControls
Workbooks("Members.xlsm").Activate
Cells(vLastRow, vColumn).Select
ActiveCell.Value = oCC.Range.Text
vColumn = vColumn + 1
Next
vColumn = 1
vLastRow = vLastRow + 1
vFileName = myDoc.Name
wdApp.ActiveDocument.Close
Name fsFile As pFolder & "Processed\" & vFileName
Next
wdApp.Quit
End Sub
This is working, but from some other reading that I have done, I know that
you are supposed to be able to get at the data in a Word2007 format file
without having to actually open the file with the Word application. I would
like to learn how to do that, but I don't know where to start. Does anyone
has some example code or point me to a working example?
Thanks.
I have managed to cobble together some code (bits and pieces found on the
internet) that extracts data from a collection of Word2007 documents and
puts that data into an Excel workbook. The source of the data is
ContentControls in the individual documents. I have a folder for
unprocessed documents and one for documents that have been processed. Here
is the code:
Option Explicit
Public Const pFolder As String = "F:\My Documents\Word\Data Extractor\"
Sub ExtractData()
Dim oCC As ContentControl
Dim fso As Scripting.FileSystemObject
Dim fsDir As Scripting.Folder
Dim fsFile As Scripting.File
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim vColumn As Integer
Dim vLastRow As Integer
Dim x As Integer
Dim vFileName As String
vLastRow = ActiveSheet.UsedRange.Rows.Count + 1
vColumn = 1
Set fso = New Scripting.FileSystemObject
Set fsDir = fso.GetFolder(pFolder & "To Process")
Set wdApp = New Word.Application
wdApp.Visible = True
For Each fsFile In fsDir.Files
wdApp.Documents.Open (fsFile)
Set myDoc = wdApp.ActiveDocument
For Each oCC In wdApp.Documents(myDoc).ContentControls
Workbooks("Members.xlsm").Activate
Cells(vLastRow, vColumn).Select
ActiveCell.Value = oCC.Range.Text
vColumn = vColumn + 1
Next
vColumn = 1
vLastRow = vLastRow + 1
vFileName = myDoc.Name
wdApp.ActiveDocument.Close
Name fsFile As pFolder & "Processed\" & vFileName
Next
wdApp.Quit
End Sub
This is working, but from some other reading that I have done, I know that
you are supposed to be able to get at the data in a Word2007 format file
without having to actually open the file with the Word application. I would
like to learn how to do that, but I don't know where to start. Does anyone
has some example code or point me to a working example?
Thanks.