Using Content Control Checkboxes to open associated excel files

shaztastic · #1 08-24-2018, 11:52 AM

Hi

I'm really struggling with this, code is not my thing any help or pointers really appreciated

I have a word 2010 document. I have a list of documents each with an associated checkbox. If a checkbox is checked I want to open the associated document, which is an excel file, when I click on a command button.

So i have an Active X checkbox with this code which opens the excel file as soon as i check it which is not quite what i want to do but i seem to keep going in circles (if possible i would like to use ContentControl Checkboxes but i cant even get the code I've got to work with them)......

Code:

 
Private Sub CheckBox1_Click()
 Dim oExcel As Excel.Application
    Dim oWB As Workbook
     Set oExcel = New Excel.Application
     Set oWB = oExcel.Workbooks.Open("C:\Temp\MyDoc.xls")
     oExcel.Visible = True
End Sub

macropod · #2 08-24-2018, 04:49 PM

To work with multiple content control checkboxes, you might use code like:

Code:

Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean)
Dim StrNm As String
Select Case CCtrl.Title
  Case "Check1": StrNm = "C:\Temp\MyWorkbook1.xls"
  Case "Check2": StrNm = "C:\Temp\MyWorkbook2.xls"
  Case "Check3": StrNm = "C:\Temp\MyWorkbook3.xls"
  Case "Check4": StrNm = "C:\Temp\MyWorkbook4.xls"
  Case Else: Exit Sub
End Select
Dim xlApp As New Excel.Application
With xlApp
  .Visible = True
  .Workbooks.Open (StrNm)
  .Activate
End With
End Sub

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

shaztastic · #4 08-25-2018, 05:16 AM

That works like a dream, I can't thank you enough

The only small downside is the Tag Property is limited to 64 characters and in reality my filename and path exceed this limit is there any way around this?

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.

shaztastic · #6 08-26-2018, 07:13 AM

You truly are a genius!

All excel files are in same location so have used the fixed location option with the filename as the Tag Property.

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