How can I identify the document that contains an embedded chart?

Question

I have a COM add-in for Excel that works with the active workbook, and while it mostly works I'm having trouble with one particular scenario.

If I have a chart in a Word 2010 document that I created using Insert->Chart, then when I click Chart Tools->Design->Edit Data, it opens up the workbook that contains the data for the chart. My add-in can (mostly) work with that workbook.

However, my add-in needs to know the folder in which the workbook is saved, and in this scenario I'd like to use the folder in which the containing document (Word document or PowerPoint presentation) is saved. But, I cannot find a way to get any information about the container document - in fact, I can't even find a way to tell that the workbook I'm dealing with is embedded in a document.

Are there any methods that would give me access to the container document?

Answer 1

Let's give this another shot...

Create another add-in (I will give an example of an XLAM) then or you could probably modify your COM add-in to this, or to ensure that this new add-in is running. This add in will trap the Application-level App_WorkbookActivate event, and check the new workbook name to see if it likely comes from Microsoft Word.

If the workbook meets this criteria, since it is triggering the App_WorkbookActivate event, then it seems reasonably safe to assume that the current ActiveDocument in MS Word is the container.

Then, we just set some object variables to capture the Word.Application and then just a matter of getting the ActiveDocument, it's .Path and it's .Name, which we can store these values in Named variables within the Excel workbook.

You can view them in the Names manager, or access them programmatically by reference to their name, .Names("docPath") and .Names("docName").

Put this in a standard module in the XLAM file:

Sub Auto_Open()
    Application.Run "ThisWorkbook.Workbook_Open" 'just in case
End Sub

The following code goes in the ThisWorkbook module of the XLAM file:

Option Explicit
Private WithEvents App As Application
Dim dictWorkbooks As Object
Private Sub Workbook_Open()
    '## Instantiate the public variables for this Add-in:
    Set App = Application
    Set dictWorkbooks = CreateObject("Scripting.Dictionary")
End Sub

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
    '## Attempt to determine if a Workbook is opened from MS Word,
    '   and if so, store the container document's path & name in
    '   named variable/range in the Workbook.
    Dim wdApp As Object
    Dim wdDoc As Object
    Dim docPath As String
    Dim docName As String
    Dim w As Integer

    If Wb.Name Like "Chart in Microsoft Word" Then
        'Get out of here if we already have this workbook activated.
        If dictWorkbooks.Exists(Wb.Name) Then Exit Sub
        Set wdApp = GetObject(, "Word.Application")
        Set wdDoc = wdApp.ActiveDocument
        docPath = wdDoc.Path
        docName = wdDoc.Name
        dictWorkbooks.Add Wb.Name, docName

        With Wb
            On Error Resume Next
            .Names("docPath").Delete
            .Names("docName").Delete
            On Error GoTo 0
            .Names.Add Name:="docPath", RefersToR1C1:=docPath
            .Names("docPath").Comment = "A variable stores the parent DOC file's path"
            .Names.Add Name:="docName", RefersToR1C1:=docName
            .Names("docName").Comment = "A variable stores the parent DOC file's name"
        End With
    End If

End Sub
Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    '## If you open multiple charts from the word document, without closing the Workbook
    ' they will be assigned unique names. However, if you open & close multiple Workbook
    ' they will all have the same name "Chart in Microsoft Word".  This method will
    ' remove an existing Key from our Dictionary when a workbook is closed, in order to
    ' prevent false matches.
    If dictWorkbooks.Exists(Wb.Name) Then _
        dictWorkbooks.Remove Wb.Name
End Sub

Answer 2

I'm pretty sure that a chart created via the Insert | Chart ribbon is not a linked document, and as such, it doesn't have a .Path property. You shouldn't need to have a path, you can access the workbook and the chart object directly:

To access the ChartData workbook/worksheet object, you can do something like this:

Sub OpenChartData(shp as InlineShape)


Dim shp As InlineShape
Dim cht As Chart
Dim wb As ChartData

    If shp.Type = wdInlineShapeChart Then
        Set wb = shp.Chart.ChartData
        Set cht = shp.Chart  '## In case you need to manipulate the Chart options like Title, Axes, etc... you can use this variable.
        wb.Activate  '## Activate the chart's ChartData sheet

        ' do stuff to the worksheet

        wb.Workbook.Application.WindowState = -4140  '## Hide the ChartData when you're finished.
    End If

End Sub

Update from Comments:

The chart data is either a linked workbook, or it is not. If it is not a linked workbook, then it does not exist somewhere as a "saved" file with a full path, etc.

To Answer Your Question:

Are there any methods that would give me access to the container document?

You have access to the container document via the ChartData object.

However, you do not want to use this object, because you insist, or erroneously understand that the workbook is "saved" somewhere, and it is merely a question of identifying where this workbook is saved, in order for your macro/add-in to work without any modifications.

Your assumption is incorrect. The workbook is not saved somewhere, it is entirely encapsulated in the ChartData object, which is part of the Microsoft Word Object Model.

From MSDN

A new object, ChartData, has been added to the VBA object models for Word to provide access to the underlying linked or embedded data for a chart. Each chart has, associated with it, the data used to draw the chart in Word. The chart data can either be linked from an external Excel workbook, or embedded as part of the chart itself. The ChartData object encapsulates access to the data for a given chart in Word. For example, the following VBA code example displays, then minimizes, the chart data for each chart contained by the active document in Word.

http://msdn.microsoft.com/en-us/library/office/ff821389.aspx

So, your options are:

1. Re-work your macro to be compatible with the ChartData object, or
2. Create your charts in Excel and insert as OLEObjects/paste in to DOC, instead of using Word's Insert | Chart.
3. Attempt to convert all charts created perviously using Word Insert | Chart to OLEObjects.

Further reading:

Update Chart Data

Creating Charts with VBA