Microsoft Office Forums - View Single Post - [Solved] Push Word content control data and excel cells

shaztastic · 08-27-2018, 12:16 AM

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

Code:

 
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