Word Form values exported to Excel problem

Question

Word Form values exported to Excel problem

remeng (TechnicalUser)

(OP)

15 Mar 19 14:38

Hello All;

I have created a Word form that has both legacy fields and AxtiveX fields. The form needs to use both types of fields due to how it needs to operate.

Problem 1:

I followed the export steps in Link but it doesn't cause legacy check box values to be recorded if checked. Any ActiveX radio button values that are selected also are not shown. The only values that are exported are some of the comment boxes.

Problem 2:

I haven't found a way that actually allows the ActiveX values to export correctly.

Inputs:

All legacy fields are assigned a bookmark name
All ActiveX controls are assigned a name value

Goal:

My goal is to write a macro that can export all (Legacy and ActiveX) of the values to specific cells in the excel document. If I need to export to Text / CSV, that works too since I can delimit them in Excel if absolutely needed.

Thanks in advance,

Mike

SkipVought (Programmer) · Answer 1 · 2019-03-15 17:59:42

Hi,

The link that you posted contains steps to SAVE a “Word Form” (which does not include ActiveX control values). The “Word Form” values are EXPORTED to a .csv file, which are then be IMPORTED and parsed into your sheet. This happens as a block of data, not consecutive rows of data.

Your ActiveX control values would then need to be processed AFTER that EXPORT/IMPORT has taken place.

Alternatively, your code could read each row in Word both Word Form fields and ActiveX controls and write to your Excel sheet.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

remeng (TechnicalUser) · Answer 2 · 2019-03-15 19:00:52

Hi Skip,

Thank you as always for your assistance. Is there a starting point that would get me going in the correct direction with regards to VBA?

Currently I can "record' a macro with the save to .csv which would start to address part of the problem. What can I start with for the ActiveX conditions?

Thanks,

Mike

SkipVought (Programmer) · Answer 3 · 2019-03-15 19:28:56

Kinda hard to answer your specific question without “seeing” what you’re working with.

How do the ActiveX controls relate to the Word Form?

If you were to EXPORT/IMPORT your Word Form table, how would you relate each ActiveX control?

Can you post an example that clearly shows that information?

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

remeng (TechnicalUser) · Answer 4 · 2019-03-15 20:13:07

hmmm... I am kind of asking about a restricted document so that is going to be interesting...

Legacy fields:
Text fields
Date fields
number values driving auto calculations in tables

ActiveX:
Pull downs
macro drivers based on selection (hide / show bookmark areas, enable / disable option radio buttons or check boxes, etc.)

hope that helps...

SkipVought (Programmer) · Answer 5 · 2019-03-15 20:28:02

No help at all!

So one way might be to loop thru the controls in your doc.

I assume that each control is related to some other group of data (row).

So knowing that, or having created some kind of MAP that describes that, you read/write those values in that loop from the form to the imported Word Form table in Excel.

What happens in vagueness, stays in vagueness!

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

SkipVought (Programmer) · Answer 6 · 2019-03-15 20:33:12

In addition to what I just suggested...

Are each of these ActiveX controls associated with one row of data in the Word Form or related some other way?

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

remeng (TechnicalUser) · Answer 7 · 2019-03-15 20:45:20

Here is the code that is driving the form. I can't release the form though. I've also stripped out any notes that are not releasable or other similar info.

Almost anywhere with a = value is an ActiveX triggered event.

BTW - thanks for the help previously to get this form working. You helped out a lot.

Mike

CODE

Private Sub pass_unlock()

'Unlocks the form.  DO NOT CHANGE THE PASSWORD

If activedocument.ProtectionType <> wdNoProtection Then

    activedocument.Unprotect Password:="####"
    
End If


End Sub


Private Sub pass_lock()

'Locks the form.  DO NOT CHANGE THE PASSWORD

activedocument.Protect Type:=wdAllowOnlyFormFields, noreset:=True, Password:="####"

End Sub


Private Sub TOC()

' Macro updates the Table of Contents

Application.ScreenUpdating = False

Call pass_unlock

activedocument.TablesOfContents(1).Update


Call pass_lock

Application.ScreenUpdating = True


End Sub


Private Sub autoclose()

' Runs the Table of Contents update macro before the document is closed

Call TOC

End Sub





Private Sub spell_check_Click()

'starts the form spell check

SP_Check = MsgBox("Would you like to start running Spell Check?", vbYesNo, "Spell Check")



If SP_Check = vbYes Then

MsgBox "Spell Check will begin shortly.  Your Screen may flash as it runs.  This is normal.", , "Spell Check"

Application.ScreenUpdating = False

Call pass_unlock

    Dim iCnt As Integer
        
    For iCnt = 1 To activedocument.FormFields.Count
                 'Select formfield
                activedocument.FormFields(iCnt).Select
                 
                #If VBA6 Then
                     'Only Word > 2000
                    Selection.NoProofing = False
                #End If
                 'Set Language
                Selection.LanguageID = wdEnglishUS
                 'Run spell checker
                Selection.Range.CheckSpelling
    
    Next

Call pass_lock

Application.ScreenUpdating = True

Else

End If

MsgBox "Spell Check has finished", , "Spell Check Finished"

End Sub
Private Sub dev_add_3PL_Click()


If dev_add_3PL = True Then

activedocument.Bookmarks("three_PL_location").Range.Font.Hidden = False

Else

activedocument.Bookmarks("three_PL_location").Range.Font.Hidden = True

End If

End Sub

Private Sub dev_add_P1_Click()

Call dev_add_3PL_Click

End Sub

Private Sub dev_add_P2_Click()

Call dev_add_3PL_Click

End Sub

Private Sub dev_add_P3_Click()

Call dev_add_3PL_Click
 
End Sub

Private Sub document_open()

Call TOC

End Sub


Private Sub Other_Add_Click()

Call dev_add_3PL_Click

End Sub

Private Sub stackable()



End Sub


Private Sub lifting()


If remove_vacuum Or remove_crane = True Then

activedocument.Bookmarks("lifting_plan").Range.Font.Hidden = False

Else

activedocument.Bookmarks("lifting_plan").Range.Font.Hidden = True

End If

End Sub

Private Sub remove_crane_Click()

Call lifting

End Sub

Private Sub remove_manual_Click()

Call lifting

End Sub

Private Sub remove_other_Click()

Call lifting

End Sub

Private Sub remove_vacuum_Click()

Call lifting

End Sub


Private Sub return_add()


If pack_type_return Or pack_type_both = True Then

activedocument.Bookmarks("return_address").Range.Font.Hidden = False
return_resp_sup.Enabled = True
return_resp_alstom.Enabled = True
return_resp_na.Enabled = False


return_resp_na.Value = False

Else

activedocument.Bookmarks("return_address").Range.Font.Hidden = True

return_resp_sup.Enabled = False
return_resp_alstom.Enabled = False
return_resp_na.Enabled = True

return_resp_sup.Value = False
return_resp_alstom.Value = False
return_resp_na.Value = True


End If

End Sub

Private Sub comp_resp_alstom_Click()

Call return_add

End Sub

Private Sub pack_type_both_Click()

Call return_add

End Sub

Private Sub pack_type_return_Click()

Call return_add

End Sub


Private Sub HU_required_Click()

If HU_required = False Then

pu_crate.Locked = True
pu_crate.Value = False
pu_box.Locked = True
pu_box.Value = False
pu_tote.Locked = True
pu_tote.Value = False
pu_crate.Locked = True
pu_crate.Value = False
pu_tray.Locked = True
pu_tray.Value = False
pu_bag.Locked = True
pu_bag.Value = False
pu_other.Locked = True
pu_other.Value = False


pumat_wood.Locked = True
pumat_wood.Value = False
pumat_plastic.Locked = True
pumat_plastic.Value = False
pumat_metal.Locked = True
pumat_metal.Value = False
pumat_foam.Locked = True
pumat_foam.Value = False
pumat_other.Locked = True
pumat_other.Value = False


activedocument.Bookmarks("PU_Pack").Range.Font.Hidden = True


Else

pu_crate.Locked = False
pu_box.Locked = False
pu_tote.Locked = False
pu_tray.Locked = False
pu_bag.Locked = False
pu_other.Locked = False

pumat_wood.Locked = False
pumat_plastic.Locked = False
pumat_metal.Locked = False
pumat_foam.Locked = False
pumat_other.Locked = False


activedocument.Bookmarks("PU_Pack").Range.Font.Hidden = False

End If

Call TOC

End Sub

Private Sub stackable_selection()

If no_stack = True Then

stack_2 = False
stack_2.Enabled = False

stack_3 = False
stack_3.Enabled = False

stack_other = False
stack_other.Enabled = False

Else

stack_2.Enabled = True
stack_3.Enabled = True
stack_other.Enabled = True

End If


End Sub

Private Sub no_stack_Click()

Call stackable_selection

End Sub

Private Sub stack_click()

Call stackable_selection

End Sub


Private Sub cardboard()
'Selects or de-selects cardboard as the HU or HU material

If hu_cardboard Or hu_cardboard1 = True Then

hu_cardboard = True
hu_cardboard1 = True

End If

End Sub

Private Sub hu_cardboard1_Click()

If hu_cardboard1 = False Then

hu_cardboard1 = False
hu_cardboard = False

Else

Call cardboard

End If

End Sub

Private Sub hu_cardboard_Click()

If hu_cardboard = False Then

hu_cardboard1 = False

Else

Call cardboard

End If

End Sub

Private Sub cost()
'Shows / hides the bookmarks for the DAP, FCA, or SAW costs

If dap = True Then

activedocument.Bookmarks("DAP_Cost").Range.Font.Hidden = False
activedocument.Bookmarks("FCA_Cost").Range.Font.Hidden = True
activedocument.Bookmarks("SAW_Cost").Range.Font.Hidden = True

Else

    If FCA = True Then

    activedocument.Bookmarks("DAP_Cost").Range.Font.Hidden = True
    activedocument.Bookmarks("FCA_Cost").Range.Font.Hidden = False
    activedocument.Bookmarks("SAW_Cost").Range.Font.Hidden = True

    Else
    
    If saw_inc = True Then
    
        activedocument.Bookmarks("DAP_Cost").Range.Font.Hidden = True
        activedocument.Bookmarks("FCA_Cost").Range.Font.Hidden = True
        activedocument.Bookmarks("SAW_Cost").Range.Font.Hidden = False

        End If

    End If

End If

End Sub


Private Sub dap_Click()

Call cost


End Sub

Private Sub fca_click()

Call cost

End Sub


Private Sub saw_inc_Click()

Call cost

End Sub

Private Sub reset_cost()
'Shows the bookmarks for the DAP, FCA, or SAW costs.  Resets radio buttons for cost

dap = False
FCA = False
saw_inc = False

activedocument.Bookmarks("DAP_Cost").Range.Font.Hidden = False
activedocument.Bookmarks("FCA_Cost").Range.Font.Hidden = False
activedocument.Bookmarks("SAW_Cost").Range.Font.Hidden = False

End Sub

Private Sub ISPM15_Click()

If ISPM15 = True Then

activedocument.Bookmarks("TCT").Range.Font.Hidden = False

Else
    
activedocument.Bookmarks("TCT").Range.Font.Hidden = True
    

End If

Call TOC

End Sub


Private Sub ref_doc_Click()

'Focuses on the Reference Document unlocked section 7 - Reference Documents - Attachment (if section number changes, update Count:= # to reflect the change.

Selection.GoTo What:=wdGoToSection, Which:=wdGoToFirst, Count:=7, Name:=""



'add reference docunment into reference document section

    Selection.InlineShapes.AddOLEObject ClassType:="Package", FileName:= _
        "", LinkToFile:=False, _
        DisplayAsIcon:=False

Call TOC

End Sub

Private Sub TCT_Ref_Click()

If TCT_Ref = True Then

activedocument.Bookmarks("TCT").Range.Font.Hidden = False

Else


activedocument.Bookmarks("TCT").Range.Font.Hidden = True

End If

Call TOC

End Sub

SkipVought (Programmer) · Answer 8 · 2019-03-15 22:33:51

Not going to try to divine what your Word Form/ActiveX Control layout/relationships are from your VBA.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

macropod (TechnicalUser) · Answer 9 · 2019-03-17 06:56:07

See: http://www.vbaexpress.com/forum/showthread.php?404.... That code concerns content controls. Further down in the thread: http://www.vbaexpress.com/forum/showthread.php?404... are mods needed for formfield data extraction. For ActiveX controls, you'd need to loop through the InlineShapes collection and test each InlineShape's properties.

Cheers
Paul Edstein
[MS MVP - Word]

remeng (TechnicalUser) · Answer 10 · 2019-03-22 19:10:10

Hi Folks,

I tried to understand the code, but I am really new to it. Can someone assist me with what each section does? I did try and run the code, but I get a lot of errors.

Thanks,

Mike

macropod (TechnicalUser) · Answer 11 · 2019-03-22 21:37:07

I doubt anyone here is interested in playing guessing games. You need to at least say what code you're running, what changes you've made to it and what errors you're getting.

Cheers
Paul Edstein
[MS MVP - Word]

remeng (TechnicalUser) · Answer 12 · 2019-03-25 14:10:02

All of the DIM error out and since I have no idea as to what they are really trying to do, I have no idea as to how to correct it or make it specific to my needs.

CODE

Sub GetFormData()
'Note: this code requires a reference to the Word object model
Application.ScreenUpdating = False
Dim wdApp As New Word.Application, wdDoc As Word.Document, CCtrl As Word.ContentControl
Dim strFolder As String, strFile As String, WkSht As Worksheet, i As Long, j As Long
strFolder = GetFolder
If strFolder = "" Then Exit Sub
Set WkSht = ActiveSheet
i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
strFile = Dir(strFolder & "\*.docx", vbNormal)
While strFile <> ""
  i = i + 1
  Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
  With wdDoc
    j = 0
    For Each CCtrl In .ContentControls
      With CCtrl
        Select Case .Type
          Case Is = wdContentControlCheckBox
           j = j + 1
           WkSht.Cells(i, j).Value = .Checked
          Case wdContentControlDate, wdContentControlDropdownList, wdContentControlRichText, wdContentControlText
           j = j + 1
           WkSht.Cells(i, j).Value = .Range.Text
          Case Else
        End Select
      End With
    Next
    .Close SaveChanges:=False
  End With
  strFile = Dir()
Wend
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
Application.ScreenUpdating = True
End Sub

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

Andrzejek (Programmer) · Answer 13 · 2019-03-25 14:52:32

"All of the DIM error " Do you mean errors related to these:
Dim wdApp As New Word.Application, wdDoc As Word.Document, CCtrl As Word.ContentControl
If so, do you have "a reference to the Word object model" ?

---- Andy

There is a great need for a sarcasm font.

macropod (TechnicalUser) · Answer 14 · 2019-03-25 20:35:30

remeng: Did you read the first line of the code???

Cheers
Paul Edstein
[MS MVP - Word]

remeng (TechnicalUser) · Answer 15 · 2019-03-26 17:17:32

Yes I did and I have no idea as to what it means. Like I said I am not a coder and I typically stumble through coding and somehow make it work with a lot of help from this forum.

Andrzejek (Programmer) · Answer 16 · 2019-03-26 17:39:19

"I have no idea as to what it means" You know, you can always do this so you can find this (for example)

---- Andy

There is a great need for a sarcasm font.

macropod (TechnicalUser) · Answer 17 · 2019-03-28 03:31:17

Quote (remeng)
Yes I did and I have no idea as to what it means. Like I said I am not a coder and I typically stumble through coding and somehow make it work with a lot of help from this forum.

With the number of threads you've started here using VBA code, no-one would have guessed you're not at least moderately competent with coding. In any event, given the amount of code you've been using, it's about time you progressed beyond a 'I typically stumble through' approach.

Cheers
Paul Edstein
[MS MVP - Word]

Come Join Us!

Posting Guidelines

Jobs

Word Form values exported to Excel problem