Push Word content control data and excel cells

[shaztastic]

Hi, not sure if this is the right forum maybe should be in the excel forum, but any help will be appreciated....



I have a Word 2010 document with a number of rich text content controls. I want to populate certain cells with specific content controls of the excel file as it opens (the word document will also be open).

i.e.

Word content control CC1 data to excel cell C5
Word content control CC2 data to excel cell C6
Word content control CC5 data to excel cell E1

I can only seem to find code that copies all content control data to an excel row. Also the filename of the word document wont always be the same or in the same location...................

[macropod]

There are numerous unexplained issues here. For example:
What determines the opening of the Excel file? If code in the document doesn't open it, how do you guarantee the correct document is active? How do you ensure all the required content controls have the correct content? What happens if the workbook is opened and the relevant document isn't the active one (Word might not even be running)?

[shaztastic]

So the excel file(s) are opened from the code in word (see below) depending on which checkboxes are checked.
Is it possible maybe to check that the required content controls have at least some data (it would be up to the user to make sure its correct)? I am only interested in 5 out of maybe 30 of the content controls in the word document.
I only want the data to be pushed to excel if the workbooks are opened from the code within the word document.
Sorry for not explaining it better and thanks for taking the time to look at this

 
Sub Open_CheckedCCs_Click()
Dim aCC As ContentControl, xlApp As Object, xlWkBk As Object
  Dim sPath As String, sFullPath As String
    sPath = "C:\Temp\MyDocs\"    'xl files in fixed location
  On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
  On Error GoTo 0
  For Each aCC In ActiveDocument.Range.ContentControls
    If aCC.Type = wdContentControlCheckBox Then
      sFullPath = sPath & aCC.Tag
      If aCC.Checked And fFileExists(sFullPath) Then
        If xlApp Is Nothing Then Set xlApp = CreateObject("Excel.Application")
        Set xlWkBk = xlApp.Workbooks.Open(sFullPath)
        xlApp.Visible = True
        xlWkBk.Activate
      End If
    End If
  Next aCC
  
End Sub

Function fFileExists(sPath As String) As Boolean
  Dim fso As Object
  Set fso = CreateObject("Scripting.FileSystemObject")
  fFileExists = fso.FileExists(sPath)
End Function

[Guessed]

This is not actually any clearer.

So that code is run from Word and will open a variable series of Excel files. When that happens, what data needs to move and in what direction?

I have a feeling that what you are trying to do is better done a different way. For instance, Rich Text CCs are not a good idea if you are expecting to move data between applications. I would be using Plain Text CCs to ensure the complexity is minimised.

[shaztastic]

When the excel files open, from the code in word, I want to copy data from the specific CCs in word (which are Plain Text not Rich my mistake) to certain cells in the excel files (they are the same cell references in the all of the excel files).

Just as an aside could you recommend a reference book that covers the basics?

[eduzs]

If you want to fill a worksheet with data from word, it's not more easy to call word from excel ?

[macropod]

Originally Posted by eduzs

If you want to fill a worksheet with data from word, it's not more easy to call word from excel ?

Not when you want to update a workbook from a currently-open document. Besides which, if you read the question, the OP only wants to update specific Excel cells, not "fill a worksheet with data from word"...

[Guessed]

You still haven't given enough information so I'm just going to guess what it is you want to do.

Continuing the theme, I would set up each of the Text Content Controls with a Tag that includes both the sheet and cell address in the form "Sheetname!A2". Then I would alter the previous code to...

Sub Open_CheckedCCs_Click()
Dim aCC As ContentControl, xlApp As Object, xlWkBk As Object
  Dim sPath As String, sFullPath As String
    sPath = "C:\Temp\MyDocs\"    'xl files in fixed location
  On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
  On Error GoTo 0
  For Each aCC In ActiveDocument.Range.ContentControls
    If aCC.Type = wdContentControlCheckBox Then
      sFullPath = sPath & aCC.Tag
      If aCC.Checked And fFileExists(sFullPath) Then
        If xlApp Is Nothing Then Set xlApp = CreateObject("Excel.Application")
        Set xlWkBk = xlApp.Workbooks.Open(sFullPath)
        xlApp.Visible = True
        xlWkBk.Activate
        UpdateXL xlWkBk
        'xlWkBk.Save
        'xlWkBk.Close
      End If
    End If
  Next aCC
End Sub

Function fFileExists(sPath As String) As Boolean
  Dim fso As Object
  Set fso = CreateObject("Scripting.FileSystemObject")
  fFileExists = fso.FileExists(sPath)
End Function

Function UpdateXL(xlWkBk As Object)
  Dim aCC As ContentControl, sSheet As String, sCell As String, sValue As String
  For Each aCC In ActiveDocument.Range.ContentControls
    If aCC.Type = wdContentControlText Then
      sSheet = Split(aCC.Tag, "!")(0)
      sCell = Split(aCC.Tag, "!")(1)
      sValue = aCC.Range.Text
      xlWkBk.Sheets(sSheet).Range(sCell).Value = sValue
    End If
  Next aCC
End Function

[shaztastic]

It nearly works not sure what I'm doing wrong....

If I try it with only one excel file it copies the data into the cells and then I get a 'subscript out of range' error.
If I try it on multiple excel files it copies the data into the cells for the first excel file and the stops opening any more excel files and I get the 'subscript out of range' error....

[Guessed]

It will fail because we are only guessing what it is you want to do and you haven't provided sample documents. You need to be explicit about which line it fails on (ie which line is highlighted in yellow when you choose to debug)

What is the value of each of the variables when it fails? Does the current workbook have a sheet with the right name? Is the cell address valid? Did you use an exclamation to separate the cell address from the sheet name? Did you store a valid address in the Tag property of EVERY text CC? Is this even what you want to do?

[shaztastic]

I don't get an option to debug and nothing is highlighted in yellow??

I'm only interested in a few of the content controls data maybe 5 out of the form's possible 30 or so (I've only given those ones a valid address in the tag property).

[shaztastic]

If I give every CC a valid address in the tag property it works great, but I am only interested in a few of the CC's

[Guessed]

So you need to change the loop to only look for particular text CCs. I would do this by using the CC Title property to nominate which text CCs are meant to feed the XL workbooks

Function UpdateXL(xlWkBk As Object)
  Dim aCC As ContentControl, sSheet As String, sCell As String, sValue As String
  For Each aCC In ActiveDocument.SelectContentControlsByTitle("Excel Data")
    If aCC.Type = wdContentControlText Then
      sSheet = Split(aCC.Tag, "!")(0)
      sCell = Split(aCC.Tag, "!")(1)
      sValue = aCC.Range.Text
      xlWkBk.Sheets(sSheet).Range(sCell).Value = sValue
    End If
  Next aCC
End Function

[shaztastic]

That is perfect I can not thank you enough!