Using Content Control Checkboxes to open associated excel files

Guessed · #3 08-24-2018, 08:22 PM

It looks like the requirement is not to run the macro on exit. The OP said they want to run it on the click of a command button.

I would store the path to each associated xl file in a property for each of the CCs (such as the Tag property). Then the code associated with the Command Button would be this sub. You will also need the function because it is checking the filepath actually exists before trying to open it.

Code:

Sub OpenCheckedCCs()
  Dim aCC As ContentControl, xlApp As Object, xlWkbk As Object
  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
      If aCC.Checked And fFileExists(aCC.Tag) Then
        If xlApp Is Nothing Then Set xlApp = CreateObject("Excel.Application")
        Set xlWkbk = xlApp.Workbooks.Open(aCC.Tag)
        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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Export Word Drop-Down Content Control to Excel Specific Sheet	nolanthomas32	Word VBA	4	09-19-2017 06:25 AM
Populate Content Control Dropdowns from Excel	Deirdre Kelly	Word VBA	23	09-07-2017 02:51 PM
Content Control Checkboxes	wlcdo2	Word VBA	3	01-05-2017 05:52 PM
Deleting a table from a content control -- preserving the content control	BrainSlugs83	Word Tables	8	11-14-2013 03:06 AM
Assigning Values to content control checkboxes and calculating results	creative cathy	Word Tables	13	10-07-2012 08:52 PM