Access VBA: Manipulating a Graph in a Word Document

[MattHolbrook]

I have created an Access application that uses workshop evaluation data to build reports with graphs on an Access form.

The user wants to have the reports print from Word. My question is regarding how to manipulate the graph in Word in order to place it within the document. The intention is to have 3 graphs in the document that is printed.

How do I set properties so that a graph will appear at a certain location on the document?

Here is the code that I have developed to produce the graph.

Public Sub CreateGraphDocument()
    Dim dbs As Database
    Dim rst As Recordset
    
    Set dbs = CurrentDb()
    
    Dim strSQL1      As String
    Dim strSQL2      As String
    Dim strSQL3      As String

    ' different select statement for all records
    strSQL1 = "SELECT Answer, Q4 As [Objectives], Q3 As [Usefulness], Q2 As [Quality], Q1 As [Value] FROM qryxQuestionsSummary"

    Set rst = dbs.OpenRecordset(strSQL1)
    
    Set m_objWord = New Word.Application
    Set m_objDoc = m_objWord.Documents.Add(m_strDIR & m_strTEMPLATE)
        
    InsertGraph rst
    
    m_objWord.PrintOut Background:=False
    
    m_objDoc.SaveAs Filename:=m_strDIR & "TestGraph.doc"
    Set m_objDataSheet = Nothing
    Set m_objWordChart = Nothing
    
    rst.Close

    m_objDoc.Close
    m_objWord.Quit
    
    Set m_objDoc = Nothing
    Set m_objWord = Nothing
End Sub

Private Sub InsertGraph(rst As Recordset)
   On Error GoTo InsertGraph_Err
   
   Dim intFldCount As Integer
   Dim fld As Field
   Dim lngRowCnt, lngColCnt, lngValue As Long
    
    
   Set m_objWordChart = m_objWord.Selection.InlineShapes.AddOLEObject(ClassType:="MSGraph.Chart.8", Filename:="", LinkToFile:=False, _
        DisplayAsIcon:=False).OLEFormat.Object
   
   Set m_objDataSheet = m_objWordChart.Application.DataSheet
    
   m_objDataSheet.Cells.Clear
    
 ' These are the lines to set up you row headings You can make this
 ' anything you want.
   intFldCount = 1

 ' Loop through the fields collection and get the field names.
   For Each fld In rst.Fields
       m_objDataSheet.Cells(intFldCount, 1).Value = _
        rst.Fields(intFldCount - 1).Name
        intFldCount = intFldCount + 1
   Next fld
   lngRowCnt = 1

 ' Loop through the recordset.
     Do While Not rst.EOF
        intFldCount = 1
    
     ' Put the values for the fields in the datasheet.
        For Each fld In rst.Fields
            m_objDataSheet.Cells(intFldCount, lngRowCnt + 1).Value = _
            rst.Fields(intFldCount - 1).Value
            intFldCount = intFldCount + 1
        Next fld
        lngRowCnt = lngRowCnt + 1
        rst.MoveNext
     Loop
    
    With m_objWordChart
        .ChartArea.Left = 2
        .ChartArea.Font.Size = 8
        .ChartType = xl3DBarStacked
        .Refresh
    End With
 
 ' Update the graph.
    m_objWordChart.Application.Update
    DoEvents
    
InsertGraph_Exit:
    Exit Sub

InsertGraph_Err:
    If Err.Number = 1004 Then
        m_objWordChart.Refresh
        Resume Next
    Else
        Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
    End If

End Sub

Thanks in advance
Matt Holbrook