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.


--
Greg Maxey - Word MVP

My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org

Hi Greg

Not try it yet but I think you are looking for somthing like this

Example for Excel data
http://msdn.microsoft.com/en-us/library/bb332058.aspx

I think there will be a example for Word also

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Greg Maxey" wrote in message ...
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.


--
Greg Maxey - Word MVP

My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org

Ron,

Thanks for the link. Unfortunately I don't see anything there where I could
apply my limited knowledge of VBA. I have not graduated to Visual Basic so
I don't understand the code.



Ron de Bruin wrote:
Hi Greg

Not try it yet but I think you are looking for somthing like this

Example for Excel data
http://msdn.microsoft.com/en-us/library/bb332058.aspx

I think there will be a example for Word also


"Greg Maxey" wrote in
message ...
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.


--
Greg Maxey - Word MVP

My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org

--
Greg Maxey - Word MVP

My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org

It is on my list to check out Greg.
I let you know if have any luck.

For Excel is ADO a option
http://www.rondebruin.nl/ado.htm

I have no problem with opening filtes in a loop and get what i want.
you have much more control and it is easy to code.

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Greg Maxey" wrote in message ...
Ron,

Thanks for the link. Unfortunately I don't see anything there where I could
apply my limited knowledge of VBA. I have not graduated to Visual Basic so
I don't understand the code.



Ron de Bruin wrote:
Hi Greg

Not try it yet but I think you are looking for somthing like this

Example for Excel data
http://msdn.microsoft.com/en-us/library/bb332058.aspx

I think there will be a example for Word also


"Greg Maxey" wrote in
message ...
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.


--
Greg Maxey - Word MVP

My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org

--
Greg Maxey - Word MVP

My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org

Ron,

I you do please let me know.

Ron de Bruin wrote:
It is on my list to check out Greg.
I let you know if have any luck.

For Excel is ADO a option
http://www.rondebruin.nl/ado.htm

I have no problem with opening filtes in a loop and get what i want.
you have much more control and it is easy to code.


"Greg Maxey" wrote in
message ...
Ron,

Thanks for the link. Unfortunately I don't see anything there where
I could apply my limited knowledge of VBA. I have not graduated to
Visual Basic so I don't understand the code.



Ron de Bruin wrote:
Hi Greg

Not try it yet but I think you are looking for somthing like this

Example for Excel data
http://msdn.microsoft.com/en-us/library/bb332058.aspx

I think there will be a example for Word also


"Greg Maxey" wrote in
message ...
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.


--
Greg Maxey - Word MVP

My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org

--
Greg Maxey - Word MVP

My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org

--
Greg Maxey - Word MVP

My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org