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.
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.