PDA

View Full Version : [SOLVED] Importing data from Word to Excel in non-continuous fields



julesh1975
03-21-2017, 03:07 AM
Hi

I've managed to find some code that enables me to import data from word content controls to an excel worksheet but I'm struggling to identify how I can alter this to paste the data into non-continuous fields:-



Sub AddContentControlValues()
' Add Tools > References: Microsoft Word and Microsoft Scripting Runtime
Dim vField 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 I As Integer
Dim vValue As Variant
Dim vFileName As String
Dim cell As Excel.Range
Dim inPath As String, outPath As String
Dim vBook As Variant

'inPath = "Q:\Sales Reports\Unprocessed\"
'outPath = "Q:\Sales Reports\Processed\"
inPath = ThisWorkbook.Path & "\in\"
outPath = ThisWorkbook.Path & "\out\"


Sheets("Development List").Select
With ActiveSheet
vLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row + 1
End With

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

vValue = vField.Range.Text
''''''' Workbooks("DARTS.xlsm").Activate 'Needed? Not needed if macro ran from it.
Set cell = Cells(vLastRow, vColumn)

If vField.Type = wdContentControlCheckBox Then 'Or vField.Type = wdContentControlRichText Or vField.Type = wdContentControlComboBox Or vField.Type = wdContentControlDropdownList Or vField.Type = wdContentControlText Then
Select Case vField.Tag
Case "CheckBox1"
vColumn = vColumn
If vField.Checked = True Then
vValue = "YES"
Else
vValue = "Not Checked"
End If
Case "CheckBox2"
If vField.Checked = True Then
vValue = "NO"
Else
vValue = "Not Checked"
End If
End Select
End If
cell.Value = vValue

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
End Sub

For example:-

Using the above code, the data is pasted as follows:-
Title - Column A (Tag: Title)
Raised by - Column B (Tag: RaisedBy)
Raised Date - Column C (Tag: DateRaised)
Owning Area - Column D (Tag: BusinessArea)

And I need the data pasted:-

Title - Column F
Raised By - Column G
Raised Date - Column I
Owning Area - Column J

My VBA skills are very limited so any help would be much appreciated.

Thanks in advance