Using Content Control Checkboxes to open associated excel files

Guessed · #5 08-25-2018, 07:04 PM

Ouch, that is a limitation I didn't think about. I would generally try to design the solution so that the vba code doesn't need to be edited in order to add or remove checkboxes.

Perhaps you could live with the limitation of 64 characters IF it was just the filename instead of the full path.
Are all the xl files in the same location? If so, the path could be hard-coded OR set to a variable that is relative to the user/file being used. For example, you might introduce the path as another variable and only store the name of the xl file in the Tag property.

Code:

Sub OpenCheckedCCs()
  Dim aCC As ContentControl, xlApp As Object, xlWkbk As Object
  Dim sPath As String, sFullPath As String
  sPath = ActiveDocument.Path & "\"   'xl files in same location as Word document
  'sPath = "\\Servername\Work\Longpathname\"    '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

If the xl files are all over the place, then you will probably need to add all the paths into the vba code directly like the sample code Paul provided.

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