Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You don't need to use Excel at all ! Use MS Access Charts in a report and some VBA code to put them into Powerpoint directly. There is already an example <a href="https://stackoverflow.com/questions/1040720/creating-a-powerpoint-with-graphs-from-access/1202246#1202246">here</a></p> <p>One "gotcha" is if you generate graphs in a group ie you design the report with a graph that is inside a group - so when you run the report you will get numerous graphs created.</p> <p>It is a bit tricky to get hold of each of these graphs and drop them into Powerpoint but here is some code that will take care of it. This works in Access 2003</p> <pre><code>'Loop through all the controls in this report and pickout all the graphs For Each c In pReport.Controls 'Graphs initially appear to be in an Object Frame If TypeOf c Is ObjectFrame Then 'Check the Class of the object to make sure its a Chart If Left$(c.Class, 13) = "MSGraph.Chart" Then 'Check if this graph must be cloned (required if the graph is in a group in the MS Access report) If Not IsGraphToBeCloned(pReport.Name, c.ControlName) Then InsertGraphToPptSlide c, "", pReport.Name Else InsertGraphGroupToPpt pReport.Name, c End If End If End If Next </code></pre> <p>This will find all the graphs in the report, if the graph is in a group then we call the InsertGraphGroupToPPt function.</p> <p>The trick here is that we know we have the same base graph multiple times - but populated with different data. So in Powerpoint what you need to do is paste the base graph into powerpoint slides n times - where n is the number of groups and then update the graphs query properties</p> <p>eg</p> <pre><code>Function UpdateGraphInPowerpoint(sql As String, OrigGraph As ObjectFrame, Groups As dao.Recordset, GroupName As String, ReportName As String) As Boolean //Copyright Innova Associates Ltd, 2009 On Error GoTo ERR_CGFF On Error GoTo ERR_CGFF Dim oDataSheet As DataSheet Dim Graph As Graph.Chart Dim lRowCnt, lColCnt, lValue As Long, CGFF_FldCnt As Integer Dim CGFF_Rs As dao.Recordset Dim CGFF_field As dao.Field Dim CGFF_PwrPntloaded As Boolean Dim lheight, lwidth, LLeft, lTop As Single Dim slidenum As Integer Dim GraphSQL As String Dim lGrpPos As Long 'Loop thru groups Do While Not Groups.EOF 'We want content to be added to the end of the presentation - so find out how many slides we already have slidenum = gPwrPntPres.Slides.Count OrigGraph.Action = acOLECopy 'Copy to clipboard slidenum = slidenum + 1 'Increment the Ppt slide number gPwrPntPres.Slides.Add slidenum, ppLayoutTitleOnly 'Add a Ppt slide 'On Error Resume Next 'Ignore errors related to Graph caption gPwrPntPres.Slides(slidenum).Shapes(1).TextFrame.TextRange.Text = ReportName &amp; vbCrLf &amp; "(" &amp; Groups.Fields(0).Value &amp; ")" 'Set slide title to match graph title gPwrPntPres.Slides(slidenum).Shapes(1).TextFrame.TextRange.Font.Size = 16 gPwrPntPres.Slides(slidenum).Shapes.Paste 'Paste graph into ppt from clipboard Set Graph = gPwrPntPres.Slides(slidenum).Shapes(2).OLEFormat.Object Set oDataSheet = Graph.Application.DataSheet ' Set the reference to the datasheet collection. oDataSheet.Cells.Clear ' Clear the datasheet. GraphSQL = Replace(sql, "&lt;%WHERE%&gt;", " where " &amp; GroupName &amp; " = '" &amp; Groups.Fields(0).Value &amp; "'") Set CGFF_Rs = ExecQuery(GraphSQL) CGFF_FldCnt = 1 ' Loop through the fields collection and get the field names. For Each CGFF_field In CGFF_Rs.Fields oDataSheet.Cells(1, CGFF_FldCnt).Value = CGFF_Rs.Fields(CGFF_FldCnt - 1).Name CGFF_FldCnt = CGFF_FldCnt + 1 Next CGFF_field lRowCnt = 2 ' Loop through the recordset. Do While Not CGFF_Rs.EOF CGFF_FldCnt = 1 ' Put the values for the fields in the datasheet. For Each CGFF_field In CGFF_Rs.Fields oDataSheet.Cells(lRowCnt, CGFF_FldCnt).Value = IIf(IsNull(CGFF_field.Value), "", CGFF_field.Value) CGFF_FldCnt = CGFF_FldCnt + 1 Next CGFF_field lRowCnt = lRowCnt + 1 CGFF_Rs.MoveNext Loop ' Update the graph. Graph.Application.Update DoEvents CGFF_Rs.Close DoEvents Groups.MoveNext Loop UpdateGraphInPowerpoint = True Exit Function End Function </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POPowerPoint Automation from MS Access....queries to chart?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USChris
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload