Consulting

Results 1 to 4 of 4

Thread: Help getting Word content control text to populate specific cells in Excel

  1. #1
    VBAX Newbie
    Joined
    Mar 2021
    Posts
    2
    Location

    Help getting Word content control text to populate specific cells in Excel

    I have a word form that has about 50 content controls on it, mostly rich text controls and some checkboxes. As well as an excel workbook that is formatted with 20+ worksheets.

    I'm trying to get some of the typed information from Word to export into excel in different specific spots on one worksheet in the book.

    The Word document is used as a questionnaire for me to fill out my area of the workbook so I use a fresh one of each every time there's a new client.

    I've spent the past 3 days looking for information and ended up with a somewhat working code. I have an issue with some information not populating in the excel document that I can't figure out. I also removed a lot of the-
    variables in the middle because it would be redundant.

    I know this code is ugly but it was the best I could do :/

    Sub Click_to_Export_Click()
    
    On Error Resume Next
    'this error message is so the other cells will populate
    
    
    Dim WD As Document
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    
    
    Set xlApp = Excel.Application
    Set WD = ActiveDocument
    
    
    xlApp.Visible = True
    
    
    choice = Application.FileDialog(msoFileDialogOpen).Show
    
    
    If choice <> 0 Then
    
    
        strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
    
    
        Set xlBook = xlApp.Workbooks.Open(strPath)
    
    
    End If
    
    
    'First half is to select the excel file to use
    
    
    
    
    Dim propName As Object
        Set propName = ActiveDocument.Bookmarks("i_prop_name").Range
        propName.Copy
        
        xlBook.Sheets("Input").[i_prop_name].PasteSpecial Excel.xlPasteValues
        
    Dim propAddress As Object
        Set propAddress = ActiveDocument.Bookmarks("i_prop_Address").Range
        propAddress.Copy
        
        xlBook.Sheets("Input").[i_prop_address].PasteSpecial Excel.xlPasteValues
    
    
    -------------------------Removed 6 other bookmarks here---------------------------------
    
    Dim Orig As Object
        Set Orig = ActiveDocument.Bookmarks("i_team_originator").Range
        Orig.Copy
        
    xlBook.Sheets("Input").[i_team_originator].PasteSpecial Excel.xlPasteValues
    
    
    
    
    'this error message is to inform that manual entry is needed
    
    If Err.Number <> 0 Then
    MsgBox "It looks like one or more fields was not properly transfered. Please enter Manually" & vbCrLf & "Error - " & Err.Description
    End If
    
    
    End Sub
    I know there's probably an easier way to loop and search for the info but I just started learning VBA on Monday and haven't gotten used to loops.

    Any help would be greatly appreciated, Thanks!

    p.s. I have all of this for a button if that makes any difference.

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    See, for example: [SOLVED] Extracting Word form Data and exporting to Excel spreadsheet (vbaexpress.com)
    The code there is designed to be run from Excel, harvesting data from a folder of Word documents. If you're going to update Excel from Word, though, you need to address issues such as what happens if someone edits the data after they have been exported. With the Excel macro, all records can be overwritten by the simple expedient of changing:
    i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
    to:
    i = 0
    or, if you want a header row:
    i = 1

    Instead of letting the code choose the columns for you, you could set the j value via a test of the content control's title and/or tag.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    VBAX Newbie
    Joined
    Mar 2021
    Posts
    2
    Location
    Hi Paul,

    Thanks for the response! That thread is actually where I began my research, albeit with a lot less knowledge than now, and I didn't know how to get the code to populate where I wanted it.

    Some questions I have:
    -I ran your base code (from your mentioned thread) just to see what it would return and nothing populated the excel cells no errors showing.

    -For the below quote, is it possible to loop what I want if the destination cells aren't in sequence?
    If you initially put just one document containing meaningful data in all of the content controls into the 'source' folder, the macro will go through through the content controls and output the data into the worksheet. you should then be able to marry-up the output with whatever column headings you'll want in the workbook.
    -Would I be able to marry the output up to specific cell names instead?

    -I understand the code for opening the file and the structure of the sequence. But, I can't wrap my head around what code is saying to find the text within the content controls , and more importantly: the code that instructs to transfer it to excel

    Thank you again for taking the time to respond. I've been having a great, but also frustrating time trying to learn VBA. Your help has given me a second wind in motivation.

    p.s. I'm including an example worksheet and the forum that I need to transfer data from to give more context.
    Attached Files Attached Files

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by EVilli View Post
    -Would I be able to marry the output up to specific cell names instead?
    As I said in my previous reply:
    Quote Originally Posted by macropod View Post
    Instead of letting the code choose the columns for you, you could set the j value via a test of the content control's title and/or tag.
    Quote Originally Posted by EVilli View Post
    -I understand the code for opening the file and the structure of the sequence. But, I can't wrap my head around what code is saying to find the text within the content controls , and more importantly: the code that instructs to transfer it to excel
    It simply reads the content controls directly and outputs the data to Excel via, via the single line:
     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
    In the above code, i is the row reference and j is the column reference.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

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