Consulting

Results 1 to 17 of 17

Thread: Word Content Control Data Import to Excel Table

  1. #1
    VBAX Regular
    Joined
    Jun 2017
    Posts
    7
    Location

    Word Content Control Data Import to Excel Table

    A previous post, Extracting Word form Data and exporting to Excel spreadsheet, provides the solution to open all the Word files in a folder and copy all the content control data to Excel. This is very close to what I would like to accomplish, but I would like the resulting data to be placed in a named Excel table.

    I used Macropod's code as a starting point. Attempted to modify the wdDoc portion of the code to reference the Excel table (tbl_Employees) on the Employees worksheet, but hit a snag.

    The code is in the Employees.xlsm file. Example files for extraction are the two *.docx files.

    The current code is (sadly) broken.
    I appreciate any help someone might offer.
    Thanks in advance,
    Jewel
    Attached Files Attached Files
    Last edited by jewel; 06-28-2017 at 11:20 AM. Reason: Wrong file attached

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Presumably you're referring to: http://www.vbaexpress.com/forum/show...l=1#post257696
    If you're wanting to send the data to a specific worksheet, change:
    Set WkSht = ActiveSheet
    to, for example:
    Set WkSht = Sheets("Employees")
    If you want the data to start populating in Column B instead of Column A, change:
    j = 0
    to:
    j = 1
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    VBAX Regular
    Joined
    Jun 2017
    Posts
    7
    Location

    Clarification

    Thanks for your reply, Paul, and all the great guidance you have provided in this forum.

    I actually want the data to land in the named Excel table, tbl_Employees by adding a new row to the existing table.

    I was trying to blend your approach of capturing the CC data (very elegant!) and Susan Hardkins' How to update UserForm VBA code to accommodate an Excel Table to pull the CC data into an Excel table (for some reason the forum is denying a posting if I add a hyperlink, apologies).

    She sets a variable oNewRow as follows: Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
    She further explains... In this case, I believe the oNewRow variable is more efficient because one statement inserts the input values as a new record and tells Excel where the values go.

    Using her syntax of ListRows.Add you would not have to locate the last filled cell (it seems to me).
    So this was one and there are many other reasons for wanting to use an Excel table.

    It seems to me that I am trying to address Excel in the Word portion of the macro. So maybe that is really my question?

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Presumably the Hadkins page is: http://www.techrepublic.com/article/...n-excel-table/

    To use the named Excel table approach, you might do something along the lines of (untested):
    Sub GetFormData()
         'Note: this code requires a reference to the Word object model
         'To do this, go to Tools|References in the VBE, then scroll down to the Microsoft Word entry and check it.
        Application.ScreenUpdating = False
        Dim strFolder As String, strFile As String
        Dim WkSht As Worksheet, xlRw As ListRow, c As Long
        strFolder = GetFolder: If strFolder = "" Then Exit Sub
        Dim wdApp As New Word.Application, wdDoc As Word.Document, CCtrl As Word.ContentControl
        Set WkSht = ActiveSheet
        strFile = Dir(strFolder & "\*.docx", vbNormal)
        While strFile <> ""
            Set wdDoc = wdApp.Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
            Set xlRw = WkSht.ListObjects("tbl_Employees").ListRows.Add(AlwaysInsert:=True)
            With wdDoc
                c = 0
                For Each CCtrl In .ContentControls
                    c = c + 1
                    With CCtrl
                        Select Case .Type
                            Case Is = wdContentControlCheckBox
                                xlRw.Range.Cell(c) = .Checked
                            Case wdContentControlDate, wdContentControlDropdownList, wdContentControlRichText, wdContentControlText
                                If IsNumeric(.Range.Text) Then
                                    If Len(.Range.Text) > 15 Then
                                        xlRw.Range.Cell(c) = "'" & .Range.Text
                                    Else
                                        xlRw.Range.Cell(c) = .Range.Text
                                    End If
                                Else
                                    xlRw.Range.Cell(c) = .Range.Text
                                End If
                            Case Else
                        End Select
                    End With
                Next
            End With
            wdDoc.Close SaveChanges:=False
            strFile = Dir()
        Wend
        wdApp.Quit
        Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing: Set xlRw = 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
    Last edited by macropod; 10-17-2022 at 02:10 PM. Reason: Data export enhancements
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    VBAX Regular
    Joined
    Jun 2017
    Posts
    7
    Location
    Hi Paul. Thanks again.
    However, I get the following error message: Run-time error 438, Object doesn't support this property or method. For this line: xlRw.Range.Cell(c) = CCtrl.Range.Text

    The Watch reports: Watch : : Range : <Expression not defined in context> : Empty :

    Any ideas? Or point me to the documentation for this topic?

  6. #6
    VBAX Regular
    Joined
    Jun 2017
    Posts
    7
    Location
    Ok, now having another issue. In this portion of the code:
    Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
    It waits endlessly for Word to do it's thing. I have to close Word using the Task Manager and then kill the Word process to clear this problem. The error message is "Microsoft Excel is waiting for another application to complete an OLE action."

    Also, in reference to my last comment, when I entered the line with "xlRow.Range." the intelliprompt offered Cells, but not Cell, so I changed that to:
    xlRw.Range.Cells(c) = CCtrl.Range.Text
    but of course it never quite makes it to that line.

  7. #7
    VBAX Regular
    Joined
    Jun 2017
    Posts
    7
    Location
    OK, one last thing, I thought maybe Word was left in some indeterminate state, so I rebooted, and tried to open the input Word files individually. One was corrupted so I deleted that one and tried again. But no avail. Same error waiting for OLE action.

  8. #8
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    There is no reason I can see for the code to fail at:
    Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
    Maybe you have a faulty Office installation. Try repairing it (via Windows Control Panel > Programs > Programs & Features > Microsoft Office (version) > Change > Repair).
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  9. #9
    VBAX Regular
    Joined
    Jun 2017
    Posts
    7
    Location

    Thumbs up

    I repaired Office as you suggested, and now this works. Thank you, thank you for all your guidance!

  10. #10
    VBAX Regular
    Joined
    Jun 2017
    Posts
    7
    Location
    One note, the references article is by Susan Harkins; sorry for misspelling your name Susan! Thanks for your help, too.

  11. #11
    VBAX Newbie
    Joined
    May 2018
    Posts
    3
    Location
    Hi Mr.paul
    From the previous post of

    Word Content Control Data Import to Excel Table

    Getformdata() function

    i have a small query

    how can i modify code to import CHEK BOX contents
    such as
    selecting genders
    when i click male the excel cell should get the male text.
    thanks

  12. #12
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    I have modified the code to output content control checkbox status and for enhanced numeric data handling. There is no way for the code to know what a particular checkbox's state indicates without adding further logic and testing.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  13. #13
    VBAX Newbie
    Joined
    May 2018
    Posts
    3
    Location
    I have added a form Check box.

  14. #14
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Meaning what, exactly?
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  15. #15
    VBAX Newbie
    Joined
    May 2018
    Posts
    3
    Location
    Quote Originally Posted by macropod View Post
    Meaning what, exactly?
    datepicker.jpg

    i have created template with these settings.
    i have many files with different data.
    i need to export these data to excel file.
    i have used Getformdata() function.
    i am getting all the data except the Two checkbox data.
    thank you.

  16. #16
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    I cannot tell from you image what those boxes are. For all I know they could be Wingding boxes. The macro works with content controls only; if your checkboxes are not content control checkboxes, the code won't process them.
    Last edited by macropod; 09-20-2022 at 05:25 AM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  17. #17
    VBAX Newbie
    Joined
    Apr 2021
    Posts
    1
    Location
    As a result, did you manage to optimize the code so that everything worked as it should? I wouldn't mind learning how to import data from a word document into an excel spreadsheet. However, I don't use word documents as often as, for example, various table editors. I also wanted to ask if it is possible to change your program code to import the data from excel to Google Sheets? I know it sounds a lot simpler than it probably is, but I'm still interested to find out. I'm not a programmer and don't really understand programming languages, but I still hope you can explain more details about your code and how I can use it as a simple user.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •