PDA

View Full Version : Import Active X fields in word doc to Excel



Mr.Petes
04-04-2012, 05:20 PM
Hey,

So far no one has been able to help me (you fail me yet again google).

I wanted to link you to an example on this forum but apparently my posts count is not high enough. soz :(

I think the code fails because it references form fields not ADO's. Problem is I don't really know much about referencing ADO's.


' Add Tools > References: Microsoft Word and Microsoft Scripting Runtime

Dim vField As TextBox
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 i As Integer
Dim vValue As Variant
Dim vFileName As String
Dim cell As Excel.Range
Dim inPath As String, outPath As String

inPath = "G:\6088_POG\Ent-Platform-Design\Work Package Management\References\Documentation\Process\Engagment of Service\New"
outPath = "G:\6088_POG\Ent-Platform-Design\Work Package Management\References\Documentation\Process\Engagment of Service\Processed"
'inPath = ThisWorkbook.Path & "\in\"
'outPath = ThisWorkbook.Path & "\out\"


vLastRow = ActiveSheet.UsedRange.Rows.Count + 1
vColumn = 1

Set fso = New Scripting.FileSystemObject
Set fsDir = fso.GetFolder(inPath)

Set wdApp = New Word.Application
wdApp.Visible = True

For Each fsFile In fsDir.Files
wdApp.Documents.Open (fsFile)
Set myDoc = wdApp.ActiveDocument
For Each vField In wdApp.Documents(myDoc).FormFields
vValue = vField.Result
Worksheets("Rawdata").Activate 'Needed? Not needed if macro ran from it.
Set cell = Cells(vLastRow, vColumn)

If vField.Type = 71 Then
Select Case vField.Name
Case "Check1"
vColumn = vColumn - 1
If vField.Result = "1" Then cell.Value = "YES"
Case "Check2"
If vField.Result = "1" Then cell.Value = "NO"
End Select
Else
cell.Value = vValue
End If
vColumn = vColumn + 1

Next vField

vColumn = 1
vLastRow = vLastRow + 1
vFileName = wdApp.ActiveDocument.Name
wdApp.ActiveDocument.Close
Name fsFile As outPath & vFileName
Next fsFile

wdApp.Quit

Then I tried the same as the ref link I posed at the top of the page. Any thoughts or advice appreciated. I will up a copy of the doc and excel sheet if required?

Thanks.

Mr.Petes
04-04-2012, 05:43 PM
Sorry forgot to mention this code is supposed to go through a directory full of docs, import the fields from the active x text boxes into the spreadsheet.

Kenneth Hobs
04-05-2012, 06:02 AM
Yes, short example files help us help you.

You might benefit from a review of these threads.

'FormFields
' http://www.mrexcel.com/forum/showthread.php?p=1639696
' http://www.mrexcel.com/forum/showthread.php?t=333200
' http://www.excelforum.com/excel-programming/799070-import-text-fields-from-word.html
' Content Controls
' http://www.vbaexpress.com/forum/showthread.php?t=39654